Post reply

how to know which operation fired the DML trigger

  • December 19, 2011 at 6:28 pm

    #248401

    Hi,

    If I have a DML trigger as follows:

    [font="Courier New"]CREATE TRIGGER trg_Orders

    ON sa.Orders

    AFTER INSERT, UPDATE, DELETE

    AS

    ...[/font]

    How can I know in the body of the trigger which operation fired the trigger: INSERT, UPDATE or DELETE?

  • December 19, 2011 at 6:52 pm

    #1423358

    oops misread it a little bit...

    if you want to know if it was an INSERT, UPDATE or DELETE, you simply check the INSERTED or DELETED tables.

    if rows exist in both INSERTED and DELETED, it was an update, if there are only rows in INSERTED, it was an insert, and similarly for DELETEd and delete operations.

    --assume it is an insert

    SET @INSERTUPDATE='INSERT'

    --if there's data in deleted, it's an update

    IF EXISTS(SELECT * FROM DELETED)

    SET @INSERTUPDATE='UPDATE'

    if you do not have a trace in place already, it is not possible. the information is not saved anywhere.

    you'll either need to have a trace in place in the future, or modify the trigger to also log some more information to help track it down;

    after granting SELECT to public on sys.dm_exec_connections so you can get the IP address in the trigger, this is a handy snippet in a trigger:

    --the auditing snippet below works fine in a

    --login trigger,

    --database trigger

    --or any stored procedure.

    SELECT

    getdate() AS EventDate,

    DB_NAME() AS DBName,

    HOST_NAME() AS HostName,

    APP_NAME() AS ApplicationName,

    OBJECT_NAME(@@PROCID) AS ProcedureName,

    USER_ID() AS Userid,

    USER_NAME() AS UserName,

    SUSER_ID() AS sUserid,

    SUSER_SNAME() AS sUserName,

    IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

    IS_MEMBER('db_owner') AS [Is_DB_owner],

    IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

    IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

    ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

    client_net_address AS ipaddress,

    auth_scheme AS AuthenticationType

    FROM sys.dm_exec_connections where session_id = @@spid

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 19, 2011 at 8:03 pm

    #1423364

    That sounds nice!

    Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply