After Update Trigger

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  January 5, 2016 at 3:48 pm

  #323704

  I have created an After Update trigger and wanted to know if there is a SQL table called UPDATED in 2012.

  For example I want use the query: SELECT * FROM UPDATED WHERE Active= 'Y'

  Or do I need to do a INSERTED DELETED JOIN instead?

  SELECT I.* FROM INSERTED I INNER JOIN DELETED D ON I.EID = D.EID AND WHERE Active= 'Y'

  Thank you in advance

• Hugo Kornelis

  SSC Guru

  Points: 64748

  More actions

  January 5, 2016 at 3:56 pm

  #1849702

  Meatloaf (1/5/2016)

  ---

  I have created an After Update trigger and wanted to know if there is a SQL table called UPDATED in 2012.

  No, there is not.

  Or do I need to do a INSERTED DELETED JOIN instead?

  SELECT I.* FROM INSERTED I INNER JOIN DELETED D ON I.EID = D.EID AND WHERE Active= 'Y'

  Yes, but you will need to use I.Active to avoid errors.

  Also, if people are allowed to update the value of the join column (EID in your example), the required code gets even more "interesting".

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
  

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  January 5, 2016 at 4:08 pm

  #1849706

  Thank you clarifying I appreciate it.

  My code sample I wrote quickly and I should have provided a better example.

  Thank again. 🙂