locked
Insert Trigger RRS feed

  • Question

  • Hello All,

    I am really new to triggers. I need to create an insert trigger on a table. I have a table called CacheTest. I have these columns and the data in that table

    id        SearchWord          Flag

    1         New York                1

    2         California                0

    Whenever the record with flag 0 gets inserted into the CacheTest table, A stored procedure needs to be executed.

    Please help me in achieving this..

    Thanks

    Monday, May 20, 2013 2:28 PM

Answers

  • I meant to have one procedure to insert values and call the other procedure if needed instead of doing that work in the trigger.

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


    My blog

    Monday, May 20, 2013 6:52 PM

All replies

  • CREATE TRIGGER tr_afterinsert ON tablex FOR INSERT 
    as
    
    IF EXISTS(SELECT 1 FROM INSERTED WHERE Flag=1)
     exec my_sp

    executing an sp from trigger will not be a good idea. 

    what if someone insert multiple records with falg value=0 using insert into .. select ..

    trigger will only fire once.. 

    is that what you want.. 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, May 20, 2013 2:32 PM
  • Do you need to execute this procedure for all inserted rows with flag 0 or just execute it once if at least 1 inserted row has flag 0? v.vt solution answers the second question.

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


    My blog

    Monday, May 20, 2013 3:03 PM
  • try this code :
      create Trigger triggername 
      on tableName
      for insert 
      as
      declare @flag varchar(100)
      select @flag =  Flag from inserted ;	
      if	@flag = '0'
      exec storedProc1


    Monday, May 20, 2013 3:18 PM
  • Take a look at this blog post

    Best Practice: Coding SQL Server triggers for multi-row operations

    to avoid common misconception about T-SQL triggers


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


    My blog

    Monday, May 20, 2013 3:21 PM
  • Thanks for the replies. I need to execute the procedure for all inserted rows with flag 0.

    Monday, May 20, 2013 3:26 PM
  • If you are using sql 2008+ then create  a table variable and pass that variable to SP rather than call calling the sp in a loop

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, May 20, 2013 3:40 PM
  • In that particular case you may run a cursor for all newly inserted rows with flag 0 and execute this procedure in a loop.

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


    My blog

    Monday, May 20, 2013 3:49 PM
  • @Naomi..

    Cursor inside a trigger would that be a good idea..

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Monday, May 20, 2013 3:56 PM
    Monday, May 20, 2013 3:56 PM
  • Thank you. So you are recommending cursor instead of trigger?.Is that you mean?

    Thanks

    Monday, May 20, 2013 3:57 PM
  • No, it may not be a good idea. But it depends on what the procedure is doing and why does it have to be run from the trigger?

    If the inserts into that table are controlled by some stored procedure, you may move that logic there instead of using a trigger.


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


    My blog

    Monday, May 20, 2013 3:58 PM
  • Actually the table will get the values from UI. I mean search words and the cache flag. The stored procedure will actually take the data from the table which has flag 0 and refresh that particular data and update the flag as 1.
    Monday, May 20, 2013 6:42 PM
  • I meant to have one procedure to insert values and call the other procedure if needed instead of doing that work in the trigger.

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


    My blog

    Monday, May 20, 2013 6:52 PM
  • I agree, it will be better to do it from a stored procedure if condition meets instead of calling triggers.
    Monday, May 20, 2013 9:05 PM