sql server trigger to save all user's actions

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

General SQL Server Forums

New to SQL Server Programming

sql server trigger to save all user's actions

Author

Topic

dimitros
Starting Member

1 Post

Posted - 2012-02-03 : 10:49:59

I want to make a trigger in ms sql server which saves every insert,update or delete had been made in my database and which user made the change.Something like a log file or exactly a log file. What i've made so far is this:

CREATE TRIGGER Log_Creators_Modifiers ON Quotation
FOR INSERT,UPDATE AS

DECLARE
@ChangeType NVARCHAR
BEGIN
/* 'created' for an INSERT, 'Delete' for DELETE, and 'modified' for UPDATE. */
IF INSERT()
BEGIN
SET @ChangeType ='CREATED';
END

ELSE IF UPDATE()
BEGIN
SET @ChangeType = 'MODIFIED';
END

ELSE IF DELETE()
BEGIN
SET @ChangeType = 'DELETE';
END

END
Any help pls?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 10:56:40

you should be using magic tables inserted and deleted here


CREATE TRIGGER Log_Creators_Modifiers ON Quotation
FOR INSERT,UPDATE AS 

BEGIN
INSERT INTO logTable (columns...,ACtion)
SELECT columns...,
CASE WHEN d.PK IS NULL THEN 'Insert' ELSE 'Update' END
FROM INSERTED i
LEFT JOIN DELETED d
ON d.PK = i.PK
END

PK is primary key of the table

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

Kristen
Test

22859 Posts

Posted - 2012-02-03 : 11:42:01

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources