Trigger Question

• Graeme100

  SSCertifiable

  Points: 7925

  More actions

  October 29, 2007 at 10:31 am

  #376544

  Hi,

  I have a query about triggers.

  We have a simple trigger that that will update or insert based on an updated column...simple enough.

  I use a query that updates the column which should then fire the trigger. The query I use has a derived table which incorporates a left outer join.

  I do a select top 1 from this table and it returns an ID and the updated column..all very straighforward.

  However, if I put a select * from Deleted/Inserted in the trigger to see what values are returned, I notice that the ID that is in the trigger is different to the one I am returning in my derived table (which I am assuming should be the one I am updating)

  As a solution, I have found that casting the IDs as Ints (they are already anyway) explicitly in the join within my derived table seems to produce the correct result.

  For clarity:

  UPDATE y

  SET y.col1 = getdate()

  FROM table1 y

  INNER JOIN

  (select id1,column2

  FROM Table1 a

  LEFT OUTER JOIN Table2 b on CAST(b.id AS INT) = CAST(a.ID AS INT)

  ) x ON x.ID = y.ID

  Anyone had any experience on this or know what might cause this?

  Thanks

  Graeme

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  October 29, 2007 at 4:39 pm

  #746425

  What that derived table is for?

  Your LEFT JOIN eliminates any filter on the rows, and whole table gets updated.

  Not sure thgis is what you're trying to achieve.

  Try this:

  SELECT y.ID, y.col1, getdate()

  --UPDATE y

  --SET y.col1 = getdate()

  FROM table1 y

  INNER JOIN Table2 b on b.id = y.ID

  Uncomment "UPDATE" part when you're happy with rows returned by SELECT.

  _____________
  Code for TallyGenerator

• Graeme100

  SSCertifiable

  Points: 7925

  More actions

  October 30, 2007 at 1:25 am

  #746488

  Sorry...idiot that I am..I left a bit out

  UPDATE y

  SET y.col1 = getdate()

  FROM table1 y

  INNER JOIN

  (select a.id1,a.column2

  FROM Table1 a

  LEFT OUTER JOIN Table2 b on CAST(b.id AS INT) = CAST(a.ID AS INT)WHERE b.id IS NULL

  ) x ON x.ID = y.ID

  The derived table should be selecting all records in Table a that are not in Table b.

  But say that...I have still had to Cast an ID as an INT when it is already an int for the trigger to work.

  Any thoughts

  Thanks

  Graeme

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  October 30, 2007 at 1:39 am

  #746494

  Graeme100 (10/30/2007)

  ---

  Sorry...idiot that I am..I left a bit out

  UPDATE y

  SET y.col1 = getdate()

  FROM table1 y

  INNER JOIN

  (select a.id1,a.column2

  FROM Table1 a

  LEFT OUTER JOIN Table2 b on CAST(b.id AS INT) = CAST(a.ID AS INT)WHERE b.id IS NULL

  ) x ON x.ID = y.ID

  The derived table should be selecting all records in Table a that are not in Table b.

  But say that...I have still had to Cast an ID as an INT when it is already an int for the trigger to work.

  Any thoughts

  Thanks

  Graeme

  First of all sort the matter with your ID's out.

  If they're int you don't need CAST. If you need CAST then at least one of them is not integer.

  And your UPDATE should look exactly like your verbal explanation:

  UPDATE y

  SET y.col1 = getdate()

  FROM table1 y

  WHERE NOT EXISTS (

  select 1 FROM Table2 b

  where b.id = y.ID

  )

  That's it. Nothing more.

  _____________
  Code for TallyGenerator

• Graeme100

  SSCertifiable

  Points: 7925

  More actions

  October 30, 2007 at 2:12 am

  #746503

  Thanks Sergiy..

  I hear what you are saying but the query is incidental to my problem...that is purely the means of testing my problem.

  The fact is all my IDs are INTs but for the triggers to fire correctly, I have to CAST the IDs as INTs explicitly within my query on the JOIN in order for the triggers to fire the correct value....that is my question.

  Have you ever had this happen ?

  Thanks again

  Graeme

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  October 30, 2007 at 2:41 am

  #746511

  Graeme, if you open BOL on topic "CREATE TRIGGER" you'll read that trigger is just a stored procedure.

  Don't make something supernatural from it.

  You better check the logic of your query.

  BTW, I don't see any reference to inserted/deleted tables in your trigger.

  I don't think it's right.

  Which table is this trigger on?

  _____________
  Code for TallyGenerator

• Graeme100

  SSCertifiable

  Points: 7925

  More actions

  October 30, 2007 at 5:23 am

  #746567

  Thanks for you input Sergiy..

  Not sure I made myself totally clear..apologies...

  I haven't posted the trigger I was just asking the question about the trigger not firing correctly due to my query.

  I will investigate further.

  Thanks again

  Graeme