Re: how to monitor table changes

From: AJAY R (ajuonline_at_yahoo.com)
Date: 08/12/03

  • Next message: Dejan Sarka: "Re: Login failed for user" 
    Date: Tue, 12 Aug 2003 11:22:32 +0530

    thanks a lot .
    "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in
    message news:uhzMkRZXDHA.416@tk2msftngp13.phx.gbl...
    > -- 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: Dejan Sarka: "Re: Login failed for user"

    Relevant Pages

    • [Announce] Non Invasive Kernel Monitor for threads/processes
      ... telecom and enterprise data centers that need to monitor a set of threads/ ... If it is a real-time signal, it would perform better as signals would ... +int main ... +struct kmonitor_bucket +{ ...
      (Linux-Kernel)
    • RE: [Announce] Non Invasive Kernel Monitor for threads/processes
      ... Non Invasive Kernel Monitor for threads/processes ... >If it is a real-time signal, it would perform better as signals would ... >+int main ... >+struct kmonitor_bucket +{ ...
      (Linux-Kernel)
    • Re: I dont have "type /tail=[n]" or "type /continuous" what do I do
      ... struct XABFHC inp_xab; ... int main ... if (monitor) { ... err = do_fixed; ...
      (comp.os.vms)
    • Apple Colors again (was Re: The Apple ][ Forever Anthology)
      ... If your monitor is running at another color temperature than 6500K it will be too blue and not red enough. ... The colors assume "standard" settings for hue and saturation, but many people back in the day turned up saturation; many TVs even ship with saturation set well above the norm setting, since "more colorful pictures" seems to be perceived as "better quality" by many buyers. ... static int angdeg; ... printf; ...
      (comp.emulators.apple2)
    • Re: how to monitor table changes
      ... (id int NOT NULL PRIMARY KEY, ... CREATE TRIGGER logActivity ON dbo.Customer ... Dejan Sarka, SQL Server MVP ... "AJAY R" wrote in message ...
      (microsoft.public.sqlserver.security)