Help me with the trigger problem

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 2008 Forums

Transact-SQL (2008)

Help me with the trigger problem

Author

Topic

satoshivnn
Starting Member

5 Posts

Posted - 2011-06-05 : 02:54:25

Take a this example that I have this table.

Table Goods
GoodsCode
GoodsName
RegisteredUser
UpdatedUser

And now, I wanna create a Log table to save update information or insert information on Goods table.

I wanna take data from User. If action is Update, I will take UpdatedUser to log. If Insert, I will take RegisteredUser to log. So in Trigger, how can I do?

How to solve it :( Please help me...

[Code]
CREATE TRIGGER tr_Goods ON Goods FOR INSERT, UPDATE, DELETE
AS
DECLARE @UserID int

-- date
SELECT @UserID = ????
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-05 : 03:45:19

[code]
CREATE TRIGGER tr_Goods ON Goods FOR INSERT, UPDATE, DELETE
AS
INSERT INTO Log
(other columns,
ActionUser
)
SELECT other columns...,
CASE WHEN d.<PK Col> IS NULL THEN i.RegisteredUser ELSE i.UpdatedUser END
FROM INSERTED i
LEFT JOIN DELETED d
ON d.<PK col> = i.<PK Col>
[/code]
PK col is primary key of the table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satoshivnn
Starting Member

5 Posts

Posted - 2011-06-05 : 05:29:01

If update, I want to save OldValue and NewValue into Log table...

I have Log table like this:

quote:

Table Logs
Type (it can be "U", "I", or D)
PK ( primary key )
ColumnName ( save column is changed )
OldValue
NewValue
UserID
ControlDate

How can I write trigger for table Goods? When it's data has changed by insert, update, or delete?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-05 : 08:30:57

[code]CREATE TRIGGER tr_Goods ON Goods FOR INSERT, UPDATE, DELETE
AS
INSERT INTO Log
(Type,
ColumnName,
OldValue,
NewValue,
UserID,
ControlDate
)
SELECT CASE WHEN i.<PK Col> IS NULL 'D'
WHEN d.<PK Col> IS NULL 'I'
ELSE 'U'
END,
'Col1',
d.Col1,
i.Col1,
CASE WHEN d.<PK Col> IS NULL THEN i.RegisteredUser ELSE i.UpdatedUser END,
GETDATE()
FROM INSERTED i
FULL JOIN DELETED d
ON d.<PK col> = i.<PK Col>
UNION ALL
SELECT CASE WHEN i.<PK Col> IS NULL 'D'
WHEN d.<PK Col> IS NULL 'I'
ELSE 'U'
END,
'Col1',
d.Col2,
i.Col2,
CASE WHEN d.<PK Col> IS NULL THEN i.RegisteredUser ELSE i.UpdatedUser END,
GETDATE()
FROM INSERTED i
FULL JOIN DELETED d
ON d.<PK col> = i.<PK Col>
...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satoshivnn
Starting Member

5 Posts

Posted - 2011-06-05 : 09:01:06

What is Col1, col2 in your code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-05 : 09:09:45

its different columns in your table, you need to repeat select for each columns of your table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satoshivnn
Starting Member

5 Posts

Posted - 2011-06-05 : 12:08:40

It's so long...
Don't you have another? Use while for repeat select column?
If it can be, please help me :( because in my project, I have more 100 column in one table...

satoshivnn
Starting Member

5 Posts

Posted - 2011-06-05 : 13:49:51

Another one, with your solve. When I update 1 column in Goods Table. All column will be save in table Log.

I need only column which has data is changed will be log on Table log. How can I do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-12 : 04:48:45

you need to add a where condition like

WHERE COALESCE(i.Col1,'') <> COALESCE(d.Col1,'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources