none
Delete trigger not working - please help

    Question

  • Hi,

    I have a INSERT trigger as below, which works perfectly.

    Create TRIGGER Trigger_insert
    on prov_main_table
    after insert
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select i.prov_id ,i.prov_name ,i.city ,i.[state] , 'ins' as Event_type 
        from prov_main_table a
        inner join inserted i
        on a.prov_id = i.prov_id

    Similarly, I created a Delete trigger , but that is not working. Below is the trigger.

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    after delete
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type 
        from prov_main_table a
        inner join deleted d
        on a.prov_id = d.prov_id

    can you pleas help me ?

    thanks in advance!

    Wednesday, September 11, 2013 12:19 AM

Answers

  • Hi,

    I have a INSERT trigger as below, which works perfectly.

    Create TRIGGER Trigger_insert
    on prov_main_table
    after insert
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select i.prov_id ,i.prov_name ,i.city ,i.[state] , 'ins' as Event_type 
        from prov_main_table a
        inner join inserted i
        on a.prov_id = i.prov_id

    Similarly, I created a Delete trigger , but that is not working. Below is the trigger.

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    after delete
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type 
        from prov_main_table a
        inner join deleted d
        on a.prov_id = d.prov_id

    can you pleas help me ?

    thanks in advance!

    Try

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    after delete
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type 
        from deleted d
    


    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Naomi NModerator Wednesday, September 11, 2013 3:40 AM
    • Marked as answer by vskindia Wednesday, September 11, 2013 3:44 AM
    Wednesday, September 11, 2013 2:03 AM
  • when both update and delete happens the "old" copy of the row(s) will be stored in "DELETED" table which can be accessed from inside trigger.

    So you have two options

    1. to create separate triggers for both delete and update in-case you want to log the "action"

    CREATE TRIGGER Trigger_delete on prov_main_table AFTER DELETE as insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type ) select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type from deleted d

    GO

    CREATE TRIGGER Trigger_Update
    on prov_main_table
    AFTER UPDATE
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'UPDATE' as Event_type 
        from deleted d

    OR

    a single trigger with out logging the action

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    AFTER UPDATE
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state])
        select d.prov_id ,d.prov_name ,d.city ,d.[state]  
        from deleted d


    Satheesh

    • Marked as answer by vskindia Wednesday, September 11, 2013 3:44 AM
    Wednesday, September 11, 2013 3:20 AM

All replies

  • Use below

    Alter TRIGGER Trigger_insert
    on prov_main_table
    after insert,DELETE
    as

    IF EXISTS(SELECT * FROM inserted)
    BEGIN
      
      insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
      select i.prov_id ,i.prov_name ,i.city ,i.[state] , 'ins' as Event_type
      from prov_main_table a
      inner join inserted i
      on a.prov_id = i.prov_id
    END

    IF EXISTS(SELECT * FROM deleted)
    BEGIN
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type
        from prov_main_table a
        inner join deleted d
        on a.prov_id = d.prov_id
    END;

    Wednesday, September 11, 2013 12:58 AM
  • Hi,

    Thanks for your reply. But still it is not working. I mean i am able to get the insert records into the log table, but deleted records are not inserting into log table. Below is the script i tried

    CREATE TABLE [dbo].[prov_log](
    [prov_id] [int] NULL,
    [prov_name] [varchar](30) NULL,
    [city] [varchar](30) NULL,
    [state] [varchar](30) NULL,
    [Event_type] [varchar](30) NULL,
    [username] [varchar](20) NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[prov_main_table](
    [prov_id] [int] NULL,
    [prov_name] [varchar](30) NULL,
    [city] [varchar](30) NULL,
    [state] [varchar](30) NULL
    ) ON [PRIMARY]

    insert into [prov_main_table] values (1,'prov1','kolkatta','kolkatta')
    insert into [prov_main_table] values (2,'prov2','chennai','tamilnadu')
    insert into [prov_main_table] values (3,'prov3','bangalore','karnataka')

    delete from [prov_main_table] where prov_id=1002
    --select * from [[prov_log]] --(no deleted records, only inserted records)

    please help!

    Wednesday, September 11, 2013 1:59 AM
  • Hi,

    I have a INSERT trigger as below, which works perfectly.

    Create TRIGGER Trigger_insert
    on prov_main_table
    after insert
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select i.prov_id ,i.prov_name ,i.city ,i.[state] , 'ins' as Event_type 
        from prov_main_table a
        inner join inserted i
        on a.prov_id = i.prov_id

    Similarly, I created a Delete trigger , but that is not working. Below is the trigger.

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    after delete
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type 
        from prov_main_table a
        inner join deleted d
        on a.prov_id = d.prov_id

    can you pleas help me ?

    thanks in advance!

    Try

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    after delete
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type 
        from deleted d
    


    Many Thanks & Best Regards, Hua Min

    • Proposed as answer by Naomi NModerator Wednesday, September 11, 2013 3:40 AM
    • Marked as answer by vskindia Wednesday, September 11, 2013 3:44 AM
    Wednesday, September 11, 2013 2:03 AM
  • Do you know that competent RDBMS programmers  use audit tools and do not write code like this? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, September 11, 2013 2:14 AM
  • hi, 

    thanks..But this is our requirement from my client - client is asking to write a trigger, so we have no other options but to do whatever he says...

    thanks!

    Wednesday, September 11, 2013 2:35 AM
  • hi  ,

      thanks. the  code given for delete trigger works...

    can you tell me how to do it for Update also?..I mean what is the best way to show records before updated and after updated in the log table?

    Thanks!


    • Edited by vskindia Wednesday, September 11, 2013 2:57 AM edit
    Wednesday, September 11, 2013 2:37 AM
  • when both update and delete happens the "old" copy of the row(s) will be stored in "DELETED" table which can be accessed from inside trigger.

    So you have two options

    1. to create separate triggers for both delete and update in-case you want to log the "action"

    CREATE TRIGGER Trigger_delete on prov_main_table AFTER DELETE as insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type ) select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'del' as Event_type from deleted d

    GO

    CREATE TRIGGER Trigger_Update
    on prov_main_table
    AFTER UPDATE
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state] ,Event_type )
        select d.prov_id ,d.prov_name ,d.city ,d.[state] , 'UPDATE' as Event_type 
        from deleted d

    OR

    a single trigger with out logging the action

    CREATE TRIGGER Trigger_delete
    on prov_main_table
    AFTER UPDATE
    as
        insert into prov_log (prov_id ,prov_name ,city ,[state])
        select d.prov_id ,d.prov_name ,d.city ,d.[state]  
        from deleted d


    Satheesh

    • Marked as answer by vskindia Wednesday, September 11, 2013 3:44 AM
    Wednesday, September 11, 2013 3:20 AM
  • BTW, in your INSERT trigger you also don't need to JOIN with the main table, you can simply grab new values from Inserted table.

    BTW, it's funny, but only after I saw Hua Min reply I figured why delete trigger didn't work. Shows I am tired now...


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, September 11, 2013 3:42 AM
    Moderator
  • great! let me try and hope it will work...thanks Guys! 
    Wednesday, September 11, 2013 3:44 AM