Latest modifiications of user-defined tables

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)

Latest modifiications of user-defined tables

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-03-27 : 13:14:18

Buddies,
I just want to know the latest modifiications (INSERT, UPDATE, or DELETE row(s)) over user-defined tables in a database. How can I write SQl scripts to do such thing?
Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 15:54:30

You can create an audit table to track changes in your main table. You need to configure a trigger for INSERT,UPDATE & DELETE actions which will insert records into audit table deatiling action performed and date when it was performed.

suppose if your audit table is YourTable_Audit the trigger will be like

CREATE TRIGGER YourTrigger ON YourTable
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED i
          INNER JOIN DELETED d
          ON d.PKCol=i.PKCol)
INSERT INTO YourTable_Audit (Action,DatePerformed,InitiatedUser)
VALUES ('UPDATE',GETDATE(),SUSER_SNAME())

IF EXISTS(SELECT * FROM INSERTED i
          LEFT OUTER JOIN DELETED d
          ON d.PKCol=i.PKCol
          WHERE d.PKCol IS NULL)
INSERT INTO YourTable_Audit (Action,DatePerformed,InitiatedUser)
VALUES ('INSERT',GETDATE(),SUSER_SNAME())

IF EXISTS(SELECT * FROM DELETED i
          LEFT OUTER JOIN INSERTED d
          ON d.PKCol=i.PKCol
          WHERE i.PKCol IS NULL)
INSERT INTO YourTable_Audit (Action,DatePerformed,InitiatedUser)
VALUES ('DELETE',GETDATE(),SUSER_SNAME())

END

johnsql
Posting Yak Master

161 Posts

Posted - 2008-03-27 : 16:13:26

quote:

Originally posted by visakh16

You can create an audit table to track changes in your main table. You need to configure a trigger for INSERT,UPDATE & DELETE actions which will insert records into audit table deatiling action performed and date when it was performed.

suppose if your audit table is YourTable_Audit the trigger will be like

CREATE TRIGGER YourTrigger ON YourTable
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED i
          INNER JOIN DELETED d
          ON d.PKCol=i.PKCol)
INSERT INTO YourTable_Audit (Action,DatePerformed,InitiatedUser)
VALUES ('UPDATE',GETDATE(),SUSER_SNAME())

IF EXISTS(SELECT * FROM INSERTED i
          LEFT OUTER JOIN DELETED d
          ON d.PKCol=i.PKCol
          WHERE d.PKCol IS NULL)
INSERT INTO YourTable_Audit (Action,DatePerformed,InitiatedUser)
VALUES ('INSERT',GETDATE(),SUSER_SNAME())

IF EXISTS(SELECT * FROM DELETED i
          LEFT OUTER JOIN INSERTED d
          ON d.PKCol=i.PKCol
          WHERE i.PKCol IS NULL)
INSERT INTO YourTable_Audit (Action,DatePerformed,InitiatedUser)
VALUES ('DELETE',GETDATE(),SUSER_SNAME())

END

Thank you for your reply.
If I have 300 tables in my database, then I have to write 300 such triggers?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 16:18:43

Yup. Since a trigger is made on a table and can monitor only the actions performed on it.But do you really need to monitor all actions happening on all tables?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-27 : 16:28:44

Brett posted code to generate it for you: http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

johnsql
Posting Yak Master

161 Posts

Posted - 2008-03-28 : 09:05:44

quote:
Originally posted by tkizer

Brett posted code to generate it for you: http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thank you.

johnsql
Posting Yak Master

161 Posts

Posted - 2008-03-31 : 16:26:12

quote:
Originally posted by visakh16

Yup. Since a trigger is made on a table and can monitor only the actions performed on it.But do you really need to monitor all actions happening on all tables?

If I generate one trigger for one table, then my database performance is affected, right?

Thank you,
johnsql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-31 : 17:10:21

Not necessarily.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

johnsql
Posting Yak Master

161 Posts

Posted - 2008-04-02 : 08:14:42

quote:
Originally posted by tkizer

Not necessarily.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Trigger does not affect database performance in general?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 13:39:57

Just depends on what the trigger is doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 14:10:38

What about this one for table,SP,and views

SELECT * FROM sysobjects WHERE (refdate > 'date') AND (xtype = 'U' OR xtype = 'P' OR xtype = 'V')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-02 : 15:56:13

That doesn't do what the poster wants though. He wants to track what changes were made which needs to be done via a trigger or tracing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources