Help me to Replace Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me to Replace Trigger

Author

Topic

iganesh2k2
Starting Member

6 Posts

Posted - 2007-05-31 : 02:55:04

In my application, i have to replace trigger.please find the scenario
let us consider a table
Employee
--------
1)EMP_ID
2)Column1
3)Column2
.
.
.
50)Column50
Employee table is having 1 million records. i have written update trigger on this table to track any changes in column for all rows by following code in trigger.

--This query is to check any changes in COLUMN1
SELECT @INSERTED_VAL=I.COLUMN1
@DELETED_VAL=D.COLUMN1
FROM INSERTED I,DELETED D,EMPLOYEE E
WHERE E.EMP_ID=D.EMP_ID AND E.EMP_ID=E.EMP_ID

if any changes found, then trigger will insert record to Log table for changes in every column.the structure of LOG table is as follows.
LOG
---
1)Emp_ID
2)Updated_User
3)Updated_Column
4)Before_Value
5)After_Value

Suppose if i change 2 columns of 10 Emplyee. then trigger will be fired to insert 20 rows to LOG table.for minimum number of record trigger is working fine.if i change 10,000 records, trigger takes more time (more than 2 hour to insert 20,000). as we know very well, trigger will be fired for each row.so in this case,trigger will be fired for 10,000 times which completely hangs my application.
please suggest me the new way to replace this trigger.

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-05-31 : 03:12:59

Change you log table structure and add the all the orginal columns + New Values Columns .Now in the trigger use columns_update() funtion to find out which columns updated and update the respective column in Log table.By this way u have avoid the mutiple row for mutiple column updation of single row.

you hav refer the columns_update in BOL.

iganesh2k2
Starting Member

6 Posts

Posted - 2007-05-31 : 06:11:58

i could not change the structure. this application was already in production in my client. please tell me the steps clearly. its very urgent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources