Update Trigger 2

[Marryp]

I need help on how I can do this:

Everytime there is a change on the table1 from database1, I need to insert the change in table1 from database2. Table1 has the following columns:
DB_Name
Table_Name
Affected_Column
Old_Value
New_Value
Date_Modified
Modified_By

So I will have a trigger on update in the table1. But what will be the best approach to do this if I have several columns in table1. Should I use Update(column1)....

Please help. Thanks.

 

[SQLDenis]

You have to update the whole row anyway, so just do the whole row, don't check for individual fields

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Marryp]

But I still have to know the column that was changed. Otherwise how will I do what you are suggesting.

 

[SQLDenis]

when there is an update the trigger will fire

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DotNetGnat]

something like this:

CREATE TRIGGER mytrigger
ON table1
FOR UPDATE
AS
IF UPDATE(yourcolumnname)
Begin
'write the code here...

here you are checking the column yourcolumnname has updated...

-DNG

 

[Marryp]

Database1
Table1
Column1
...
...
Column15

Database2 - a separate DB
Table1 - logs the changes in different DB for now let's just say Database1 from different tables
DB_Name
Table_Name
Affected_Column
Old_Value
New_Value
Date_Modified
Modified_By

 

[Marryp]

DotNetGnat that's the question I have earlier do I have to explicitly name the columns. What if there are several columns.

 

[DotNetGnat]

then you need to do something like this...

CREATE TRIGGER mytrigger
ON table1
FOR UPDATE
AS
IF UPDATE(yourcolumnname)
if exists(Select * FROM Inserted WHERE yourcolumn1 =somevalue or yourcolumn2=someothervalue)
Begin
'write the code here...

put as many conditions as you want...

-DNG

 

[SQLDenis]

If I understand the question correctly then DB2 is a mirror of DB1
When DB1 changes you have to make the change on DB2
If that is the case you do NOT need to check for columns because an update trigger will only fire after one of the fields have been updated
What if someone updates 6 rows are you going to move the same data 6 times?

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Marryp]

SQLDenis - DB2 is not a mirror of DB1. It is an audit log. It will track each time there is a change in the table -- who edit the record, what column was edited, what was the old value and the new value, and it will stamped the date it was modified.

DotNetGnat - is there other option other than explicitly naming the column. Just because some tables can have several columns then the script will be too long.

 

[SQLDenis]

If you need to audit by column you have to name them all, see example below


CREATE TRIGGER AuditUpdate ON dbo.Problem
FOR INSERT,UPDATE AS DECLARE @comments VarChar(255)
IF UPDATE(Status)
begin
SELECT @comments = 'Status Column modified'
INSERT
INTO AuditTrail(TableName, ActionTaken, ActionUser,
ActionDate, Comment)
VALUES ('Problem', 'U', User_Name(), GetDate(), @comments)
end
IF UPDATE(Recordedby)
begin
SELECT @comments = 'RecordedBy Column modified'
INSERT
INTO AuditTrail(TableName, ActionTaken, ActionUser,
ActionDate, Comment)
VALUES ('Problem', 'U', User_Name(), GetDate(), @comments)
end


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Marryp]

Thanks SQLDenis and DotNetGnat!

I thought there will be a workaround other than explicitly naming the columns.

Have a great weekend