SQL Server Performance Forum – Threads Archive

Should I user trigger ?

Hi all,
I need some help to determine which is best option to implement based on this scenario: Table1
id
first_name
last_name
active_status
date_created
date_modified Table1_History
id
first_name
last_name
active_status
history_comments
date_created
date_modified I need to capture whatever the changes are in the Table1 and reflect into Table1_History.
For instance, the status in table1 has been changed by application(through SP) but I need to capture these changes in Table1_History. So, should I modify the SP and insert into history table whenever they make changes? OR create an INSERT UPDATE trigger to handle this? Any hint with this trigger would be greatly appreciated.
Thanks,
DilliGrg

---

You can do without a trigger by modifying the procedure but do it in single transaction… If you are inserting, updating and modifying the data using multiple procedure you have modify all of them where if you use the trigger you don’t need to touch them.. Only three procs. you need to modify then I will prefer not using trigger… Mohammed U.

---

Hi Mohammed,
As always thanks for the reply. Ok, the basic scenario: whenever I insert record into Table1 through a insert stored procedure, it should also insert into table1_history so both of these tables will have same record at this time. When I update the existing record in Table1, I want to make the same changes in Table1_History but instead of updating, I will be inserting a new record(based on the update from Table1 value) here so now I will have two records in Table1_History table with history_comments. I can do this by modifying the 3 stored procedures (insert/update/save). Can trigger do this and how efficient will it be in a high volume application? My main concern is how can I populate history_comments column in trigger where I can pass this as optional input parameter in stored procedure. Thanks,
DilliGrg

---

]]>