Trigger or SP: what should I use in my case?

Question

I have an application written by another team in our company that inserts data into one table. Let them say that they write data to the Log1 table with the fields:

• Id (automatically generated primary key);
• KeyId;
• Value1;
• Value2;
• Value3.

Now I need to have one more additional record in another table (Log2) of them, which has only a part of its data:

• Id (this will be my own auto-generated identifier);
• KeyId;
• Value 1.

I see two ways to do this:

• Create a trigger that, when adding entries to Log1, will automatically create an entry in Log2 with the required data;
• Deploy an SP that will take all the necessary data for the Log1 table and create entries in both tables, and then ask the authors of these applications to use the SP instead of directly querying INSERT.

What do you think is the best option in this case and why?

Many thanks for your help.

PS I am using MS SQL 2005

Answer 1

If you use a trigger, keep in mind that both Log1 and Log2 seem to use identifier columns, you cannot use SELECT @@IDENTITY to return PK log 1 - you will need to use SCOPE_IDENTITY() .

On the other hand, if you use SPROC, then you can revoke the INSERT privileges for your table (approximately) for everyone and instead provide EXEC on your SPROC. Thus, access to your table should be well protected.

Answer 2

Go with option 1.

This means that the tables will be synchronized correctly, even if the “correct” interface of the stored procedure is not used, and it will be easier and more efficient to insert several rows (how do you do this using the stored procedure in SQL Server 2005? - Call it several times? First convert all data in XML format?)

Answer 3

The only way to guarantee data integrity is with a trigger. There is always a chance that someone will perform an operation (bulk operation, sql insert statement, etc.) that will bypass your SP.

Answer 4

Go with option 2.

Whenever possible, triggers should be avoided.

Unclear reason: have you ever used SQL Server replication tools? Triggers will not be very easy to replicate. (i.e. it's not as simple as a few clicks, for example, for tables). But I'm moving away from the topic ... bottom line, triggers evil ... avoid it when you can.

EDIT

Additional reasons. Triggers don't just see how other objects are in the DBMS. On the application side, they are invisible, and if they are not documented, they are usually forgotten. If there is a change in the schema ... well, it just makes things easier with stored procedures.