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

I have a application written by other team in our company that insert data in one table. Let's say they write data into table Log1 with fields:

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

For now I need to have another additional record in another table (Log2) from them that has only开发者_如何学Python part of their data:

• Id (it will be my own auto-generated Id);
• KeyId;
• Value1.

I see 2 ways to do that:

1. Create trigger that on adding records into Log1 will automatically create record in Log2 with required data;
2. Implement SP that will accept all required data for Log1 table and will create records in both tables, then ask those applications authors use SP instead of direct INSERT query.

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

Thank you very much for your help.

P.S. I'm using MS SQL 2005

Go with option 1.

It means that the tables will be synchronised properly even if the "correct" stored procedure interface isn't used and it will be easier and more efficient to insert multiple rows (How would you do this with a stored procedure in SQL Server 2005? - Call it multiple times? Convert all the data to XML format first?)

If you use a trigger, be aware that as it seems both Log1 and Log2 use identity columns, that you can't use SELECT @@IDENTITY to return the PK of Log1 - you will need to use SCOPE_IDENTITY().

On the other hand, if you use a SPROC, what you can do is revoke INSERT privileges to your table from (just about) everyone, and instead grant EXEC on your SPROC. This way access to your table should be fairly well guarded.

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

Go with option 2.

Triggers should be avoided whenever possible.

One not so obvious reason: Have you ever used SQL Server replication facilities? Triggers won't be very straightforward to replicate. (ie it is not as easy as a couple of clicks, like it is for tables for instance). But I'm going off topic ... bottom line, triggers are evil... avoid when you can.

EDIT

More reasons: Triggers are not easy to see like other objects in the DBMS. On the application side, they are invisible, and if not well documented, they tend to be forgotten. If there are changes to the schema ... oh well, it's just easier to maintain stuff with stored procedures.