June 3, 2013 at 4:38 am
Hello all,
I've created a trigger that I'm having a bit of a problem with.
After a user completes a warrantly form online, I do a lookup against an internal table to see if they are a registered installer, and mark them as so...
ALTER TRIGGER [dbo].[TRIG_Update_Installer]
ON [dbo].[tbl_warranties]
AFTER insert
AS
BEGIN
update tbl_Warranties set siteID = 'O'
where ID in (
SELECT dbo.tbl_warranties.ID
FROM dbo.Tbl_InstallersList INNER JOIN
dbo.tbl_warranties ON dbo.Tbl_InstallersList.Gas_id = dbo.tbl_warranties.business_gsn
WHERE (dbo.tbl_warranties.business_gsn IS NOT NULL)
AND (LEN(dbo.tbl_warranties.business_gsn) > 0
and datePickup is null)
)
END
If I run the code within the BEGIN, it works perfectly.. I just can't get it to run from the trigger after the record has been inserted 🙁
I've tried 'on insert' and 'after insert'
Any help would be most appreaciated..
Many thanks
Dave
June 3, 2013 at 6:30 am
I am not sure why are observing a difference in behavior
But, I see some issues in your UPDATE statement
You are updating all the rows in the table when a row is inserted which I feel is wrong
Please provide the structure of the tables involved so that I can try to re-write your query
Hopefully, that might solve your problem as well.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 3, 2013 at 6:45 am
yeah, I could have just updated the row for the individual scope ID, but there are only ever 5 records in there with a Null datefield which is being checked. As the table in whole only has 5k records, I didn't think the hit on performance is that great. I guess for tidyness I could set it for the one record.
thanks
Dave
June 3, 2013 at 6:52 am
well, I've got two suggestions:
first, you should be using the INSERTED table so you only get the rows that were touched in this INSERT.
The second, is just to review the logic for the inner join & WHERE statement; all three conditions would have to be met to update the siteID, and are you sure that is the right logic or not;:
--dbo.Tbl_InstallersList.Gas_id = INSERTED.business_gsn
--INSERTED.business_gsn IS NOT NULL
--LEN(INSERTED.business_gsn) > 0
--AND datePickup IS NULL
here's my version using the INSERTED virtual table:
ALTER TRIGGER [dbo].[TRIG_Update_Installer]
ON [dbo].[tbl_warranties]
AFTER INSERT
AS
BEGIN
UPDATE tbl_Warranties
SET siteID = 'O'
WHERE ID IN (SELECT
INSERTED.ID
FROM dbo.Tbl_InstallersList
INNER JOIN INSERTED
ON dbo.Tbl_InstallersList.Gas_id = INSERTED.business_gsn
WHERE ( INSERTED.business_gsn IS NOT NULL )
AND ( LEN(INSERTED.business_gsn) > 0
AND datePickup IS NULL ))
END
Lowell
June 3, 2013 at 7:31 am
Hi Lowell,,
Yes, its not the cleanest or logical of code.. Just thrown together to get a result.
-dbo.Tbl_InstallersList.Gas_id = INSERTED.business_gsn
--INSERTED.business_gsn IS NOT NULL
--LEN(INSERTED.business_gsn) > 0
--AND datePickup IS NULL
Ideally, I wouldn't need to check for Null fields, GSN numbers, when using inner join, but then, I'd be stupid to expect a user to not enter blank fields into the Installers table! which they have and produced many rows. to combat it, I've filted by having len(GSN) > 0 and also not Null. :rolleyes:
update tbl_Warranties set siteID = 'O'
where ID in
(
SELECT dbo.tbl_warranties.ID
FROM dbo.tbl_warranties INNER JOIN
dbo.Tbl_InstallersList ON dbo.tbl_warranties.business_gsn = dbo.Tbl_InstallersList.Gas_id
WHERE (dbo.tbl_warranties.business_gsn IS NOT NULL)
AND (LEN(dbo.tbl_warranties.business_gsn) > 0)
and pfpickup is null
and tbl_Warranties.ID in (SELECT id FROM INSERTED)
)
I thought the above would have worked, but it hasn't.... So I've commented out the last part...
Looking back over my original code..
Now this is where it gets weird... - When I populate my warrantly form, it leaves the INSERTED entry as is,, (ie, doesn't update Row ID 3432 ) however, every other entry in the table that matches the critera does get updated....
as you'd expect, the next time I add a record, 3432 now gets updated and 3433 doesn't... its like the trigger is running fine, just not seeing the current inserted row.. lol
June 3, 2013 at 8:15 am
That's because you're looking for rows in tbl_warranties before they exist. That's why Lowell suggested to use the inserted table to search for the ID.
June 3, 2013 at 8:24 am
Yes, I see what you mean, but that also creates another problem.
This won't work, because its looking for a record to update in tbl_warranties that doesn't exist yet.
update tbl_warranties set siteID = 'O'
where ID in
(
SELECT INSERTED.ID
FROM INSERTED INNER JOIN
dbo.Tbl_InstallersList ON INSERTED.business_gsn = dbo.Tbl_InstallersList.Gas_id
WHERE (INSERTED.business_gsn IS NOT NULL)
AND (LEN(INSERTED.business_gsn) > 0)
and pfpickup is null
--and tbl_Warranties.ID in (SELECT id FROM INSERTED)
)
but..... I can't update as suggested as it doesn't exist in tbl_warranties..
soo, can I updated the INSERTED virtual table before its written to the actual table?
update INSERTED set siteID = 'O'
where ID in
(
SELECT INSERTED.ID
FROM INSERTED INNER JOIN
dbo.Tbl_InstallersList ON INSERTED.business_gsn = dbo.Tbl_InstallersList.Gas_id
WHERE (INSERTED.business_gsn IS NOT NULL)
AND (LEN(INSERTED.business_gsn) > 0)
and pfpickup is null
--and tbl_Warranties.ID in (SELECT id FROM INSERTED)
)
Thanks in advance all..
really appreciate it!
Dave
June 3, 2013 at 8:27 am
I thought AFTER INSERT meant that the code ran after it had been inserted, clearly its not the case 😀
ALTER TRIGGER [dbo].[TRIG_Update_Install]
ON [dbo].[tbl_warranties]
after INSERT
AS
BEGIN
June 3, 2013 at 8:41 am
If you are just trying to set a value for all inserted rows why not make it the default? Then you don't have to worry about a trigger at all? Or you might look at an INSTEAD OF trigger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2013 at 8:44 am
hb21l6 (6/3/2013)
I thought AFTER INSERT meant that the code ran after it had been inserted, clearly its not the case 😀
That's exactly what it means. After the insert has run but within the scope of the transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2013 at 8:53 am
Thank you all for your help,,, I've found it much easier just to lookup the entry in the web page prior to writing it to the table.
Regards
Dave
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply