August 28, 2013 at 8:28 am
I do not understand why the exists() tests are not working. I'm using SQL Server 2008 R2.
Here's the code:
create trigger dbo.io_trigger_Insert_Alert
on dbo.ALERT
instead of insert
as
begin
set nocount on;
-- Row exists
if exists (select * from dbo.ALERT a, inserted i
where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType)
begin
-- Move record to history
insert into dbo.ALERT_STATUS_HISTORY (
OBJECTID, StopID, RouteID, ServiceCenterID, AlertType,
Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime,
AlertID)
select OBJECTID, StopID, RouteID, ServiceCenterID, AlertType,
Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime,
AlertID
from dbo.ALERT;
-- Update AlertStatus
if exists (select *
from dbo.ALERT a, inserted i
where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType
and a.AlertStatus = N'X')
begin
update dbo.ALERT
set AlertStatus = N'N'
from dbo.ALERT a, inserted i
where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType;
end
end
end
August 28, 2013 at 8:54 am
thomas@the_grind (8/28/2013)
I do not understand why the exists() tests are not working. I'm using SQL Server 2008 R2.Here's the code:
create trigger dbo.io_trigger_Insert_Alert
on dbo.ALERT
instead of insert
as
begin
set nocount on;
-- Row exists
if exists (select * from dbo.ALERT a, inserted i
where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType)
begin
-- Move record to history
insert into dbo.ALERT_STATUS_HISTORY (
OBJECTID, StopID, RouteID, ServiceCenterID, AlertType,
Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime,
AlertID)
select OBJECTID, StopID, RouteID, ServiceCenterID, AlertType,
Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime,
AlertID
from dbo.ALERT;
-- Update AlertStatus
if exists (select *
from dbo.ALERT a, inserted i
where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType
and a.AlertStatus = N'X')
begin
update dbo.ALERT
set AlertStatus = N'N'
from dbo.ALERT a, inserted i
where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType;
end
end
end
Why do you say the exists isn't working?
Your insert for "-- Move record to history" seems like it is not doing what you want to do. It will insert ALL rows from Alert. Did you mean to select from inserted here?
I would think about changing this entire trigger to use a single MERGE statement instead.
_______________________________________________________________
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/
August 28, 2013 at 8:59 am
I found the problem (and embarrassed I didn't check for this before). Either StopID or RouteID can be NULL. However, that brings up an interesting point. How can I avoid coding the exists() test without extending the WHERE clause to test for the NULLs?
if exists (select * from dbo.ALERT a, inserted i
where (a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType)
or (a.StopID is null and i.StopID is null and a.RouteID = i.RouteID and a.AlertType = i.AlertType)
or (a.StopID = i.StopID and a.RouteID is null and i.RouteID is null and a.AlertType = i.AlertType))
August 28, 2013 at 9:04 am
Thanks for your reply.
I found why the exists() did not work (either StopID or RouteID can be NULL).
The '--Move record to history' is used to make an archive copy of the ALERT row before it updates it.
August 28, 2013 at 8:41 pm
Hello Thomas -- a couple of comments on your query:
The move record to history section will write the entire contents of your dbo.Alert table, not just the rows upon which you are operating.
Regarding the potential for NULLs in either the StopID or the RouteID, here's a suggestion:
Use THE UNION ALL operator to break your EXISTS query into separate pieces. It's a lot more code, but I think it's much more legible than extending the WHERE clause with multiple layers of compound logic:
IF EXISTS ( SELECT 1
FROM dbo.ALERT AS a
INNER JOIN inserted AS i
ON a.StopID = i.StopID AND a.RouteID = i.RouteID AND a.AlertType = i.AlertType
UNION ALL
SELECT 1
FROM dbo.ALERT AS a
INNER JOIN inserted AS i
ON a.RouteID = i.RouteID AND a.AlertType = i.AlertType
WHERE a.StopID IS NULL AND i.StopID IS NULL
UNION ALL
SELECT 1
FROM dbo.ALERT AS a
INNER JOIN inserted AS i
ON a.StopID = i.StopID AND a.AlertType = i.AlertType
WHERE a.RouteID IS NULL AND i.RouteID IS NULL )
Please note that I changed your base query to use INNER JOINs. Your code uses the old-school style of joins. Two years ago I would have said, "Hey, I'm old-school, what's wrong with that syntax? That's how I learned back in the days of DB/2, and it still works fine!!"
After much reading here and elsewhere I'd recommend to anyone to recode the old-school equi-joins with the INNER JOIN syntax. Someone after you will have to maintain this, and you want them to think well of you...
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
August 29, 2013 at 3:06 am
ChrisCarsonSQL,
Excellent comments. I plan to apply them to this procedure.
Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply