locked
trigger after insert little help RRS feed

  • Question

  • hi guys , i need little help with a trigger

     

    i think am making a silly mistake but can not find

     

    table1 (id primarykey, id2,id3,id4,modifiedby)

     what i am trying do is am updating a column called modifiedby in table1 with  SUSER_NAME() but the problem is when i update trigger is working but when i insert a record its not updating the modifiedby column

    CREATE TRIGGER UPD ON table1
      AFTER UPDATE,INSERT
      
        AS
        
        
       BEGIN 
        
          IF EXISTS (
          
            SELECT 1 
          
          
            FROM INSERTED I INNER JOIN DELETED D ON I.ID = D.ID
            
            WHERE   I.id2 <> D.id2OR
                    I.id3 <> D.id3 OR
                    I.id4 <> D.id4)
                    
           BEGIN 
           
           
             UPDATE R
            
             SET R.MODIFIEDBY = SUSER_NAME()
             
             FROM   DBO.table1 R  INNER JOIN INSERTED I ON R.ID = I.ID     
             
             
            END
             
        END            
                    
                     
         
    

     

     

     

     

     

     


    • Edited by sql393 Tuesday, October 11, 2011 9:06 PM
    Tuesday, October 11, 2011 8:54 PM

All replies

  • your problem is here

     IF EXISTS (   SELECT 1      
            FROM INSERTED I
    	 INNER JOIN DELETED D ON I.ID = D.ID        
            WHERE   I.id2 <> D.id2 OR
                    I.id3 <> D.id3 OR
                    I.id4 <> D.id4)
                    
           BEGIN 
    end


    when an insert is fired only inserted table will have value not the deleted.  on delete, deleted table hold the previous value. on update inserted hold the new value and deleted hold the previous value.

    see my blog
    http://oneplace4sql.blogspot.com/2007/06/which-action-fired-trigger.html

    hope you can workout the logic..

    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
    Tuesday, October 11, 2011 9:10 PM
  • Remove INSERT in the FOR clause for your trigger. For Insert you can simply use DEFAULT value for ModifiedBy.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, October 11, 2011 9:17 PM
  • can you please write a little smaller? my eyes havent exploded yet.
    Pérez
    Wednesday, October 12, 2011 10:14 PM