Re: how to monitor table changes

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka_at_avtenta.si)
Date: 08/08/03

  • Next message: Ray Higdon: "how to monitor table changes" 
    Date: Fri, 8 Aug 2003 12:03:20 +0200

    -- base table
    IF OBJECT_ID('dbo.Customer','U') IS NOT NULL
       DROP TABLE dbo.Customer
    GO
    CREATE TABLE dbo.Customer
    (id int NOT NULL PRIMARY KEY,
     name varchar(30) NULL)
    GO

    /* Auditing with triggers */
    -- log table
    IF OBJECT_ID('dbo.CustomerLog','U') IS NOT NULL
       DROP TABLE dbo.CustomerLog
    GO
    CREATE TABLE dbo.CustomerLog
    (id int NOT NULL,
     name varchar(30) NOT NULL,
     loginname sysname, -- login of the actor
     username sysname, -- db user of the actor
     typeOfEvent char(1), -- type of action - I-nsert, U-pdate, D-elete
     deletedInserted char(1), -- does the row come from I-nserted or D-eleted
    table
     whenEvent datetime DEFAULT GETDATE()) -- timestamp
    GO

    CREATE TRIGGER logActivity ON dbo.Customer
    FOR INSERT, UPDATE, DELETE
    AS
    IF (SELECT COUNT(*) FROM Inserted) > 0
       IF (SELECT COUNT(*) FROM Deleted) > 0
        BEGIN
          INSERT INTO dbo.CustomerLog
           SELECT *, SUSER_SNAME(),USER_NAME(),'U','D', GETDATE()
             FROM Deleted
          INSERT INTO dbo.CustomerLog
           SELECT *, SUSER_SNAME(),USER_NAME(),'U','I', GETDATE()
             FROM Inserted
        END
       ELSE
          INSERT INTO dbo.CustomerLog
           SELECT *, SUSER_SNAME(),USER_NAME(),'I','I', GETDATE()
             FROM Inserted
    ELSE
       IF (SELECT COUNT(*) FROM Deleted) > 0
          INSERT INTO dbo.CustomerLog
           SELECT *, SUSER_SNAME(),USER_NAME(),'D','D', GETDATE()
             FROM Deleted
    GO 

    -- 
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"AJAY R" <ajuonline@yahoo.com> wrote in message
news:#g9ADaXXDHA.1832@TK2MSFTNGP09.phx.gbl...
> Hi ,
> I want to monitor insert,update,delete on a particular code through
trigger
> , I am planning to create a another table as a logtable , I can achieve it
> for update & delete can anybody give me code to achieve all
> insert,update&delete using signle trigger .
>
> Thanks & Regards
> Ajay
>
>
  • Next message: Ray Higdon: "how to monitor table changes"

    Relevant Pages

    • Re: bcp into view with derived column
      ... I have added the following line to my trigger code - ... CREATE TABLE sandell (col1 int NULL, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
      (comp.databases.ms-sqlserver)
    • Re: Solved: Columns_Updated()
      ... > DECLARE @POS INT ... > In your trigger call the function within a IF:> ... Checked with MSDE 2000 and SQL Server 2000 ...
      (microsoft.public.sqlserver.programming)
    • Non-existant Trigger Problem
      ... I am using SQL Server 2000. ... I try to modify a filed in a table definition ... (from int to bit) ... trigger could not be read and the process aborts. ...
      (microsoft.public.sqlserver.programming)
    • Re: Unexpected cascaded epoll behavior - my mistake or kernel bug
      ... level triggered epoll set. ... static int xepoll_create{ ... return epfd; ... child will trigger the wakeup chain. ...
      (Linux-Kernel)
    • Re: WHERE clause applies to right-hand table of LEFT JOIN
      ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
      (microsoft.public.sqlserver.server)
    • Quantcast