Trigger Question

Question

Post reply

Trigger Question

Graeme100

SSCrazy Eights

Points: 8054
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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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 SSCrazy Eights Points: 8054 More actions · Answer 2

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: 110208 More actions · Answer 3

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 SSCrazy Eights Points: 8054 More actions · Answer 4

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: 110208 More actions · Answer 5

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 SSCrazy Eights Points: 8054 More actions · Answer 6

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