July 21, 2009 at 8:59 am
I have a table called SRVC00200 which I created a trigger. What I am trying to do is when the field SRVSTAT is updated I want to get a value from the row that was updated and pass that value to another stored procedure.
Right now I am testing by doing the following:
update SVC00200 set SRVSTAT = 800 where CALLNBR = '0000016736'
I get the following error:
Msg 208, Level 16, State 1, Procedure tr_CHECK_SRVSTAT, Line 11
Invalid object name 'TRAIN.dbo.inserted'.
Any ideas why this doesn't work?
USE [TRAIN]
GO
/****** Object: Trigger [dbo].[tr_CHECK_SRVSTAT] Script Date: 07/21/2009 09:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_CHECK_SRVSTAT]
ON [TRAIN].[dbo].[SVC00200]
AFTER UPDATE
AS
DECLARE @CALLNBR varchar(50)
DECLARE @SRVSTAT char(3)
IF UPDATE(SRVSTAT)
BEGIN
SELECT@SRVSTAT = SRVSTAT,
@CALLNBR = CALLNBR
FROM [TRAIN].[dbo].inserted
IF @SRVSTAT = 800
BEGIN
--CALL SP THAT PASSES CALLNBR AND IF HAS NOT BEEN EMAILED THEN SEND EMAIL AND UPDATE
EXEC sp_webServiceRequest_EmailComplete @CALLNBR
END
END
July 21, 2009 at 9:05 am
a simple syntax error;
INSERTED is a virtual table that exists just inside the trigger...so there is no table called [TRAIN].[dbo].inserted [/b]
this should be:
SELECT @SRVSTAT = SRVSTAT,
@CALLNBR = CALLNBR
FROM inserted
note your trigger has a logic error in it; it would not work correctly if two or more rows were updated at the same time.
Lowell
July 21, 2009 at 9:31 am
Yes, that worked. Any documentation on handling multiples?
July 21, 2009 at 1:08 pm
Trigger will be called as many rows gets updated , so that should work ,
USE [TRAIN]
GO
/****** Object: Trigger [dbo].[tr_CHECK_SRVSTAT] Script Date: 07/21/2009 09:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_CHECK_SRVSTAT]
ON [TRAIN].[dbo].[SVC00200]
AFTER UPDATE
AS
DECLARE @CALLNBR varchar(50)
DECLARE @SRVSTAT char(3)
IF UPDATE(SRVSTAT)
BEGIN
SELECT @SRVSTAT = SRVSTAT,
@CALLNBR = CALLNBR
FROM INSERTED
IF @SRVSTAT = 800
BEGIN
--CALL SP THAT PASSES CALLNBR AND IF HAS NOT BEEN EMAILED THEN SEND EMAIL AND UPDATE
EXEC sp_webServiceRequest_EmailComplete @CALLNBR
END
END
July 21, 2009 at 2:15 pm
Triggers in SQL Server are fired once regardless of the number of rows inserted/updated/deleted. If the possibility exists, you need to code your triggers to handle multiple rows.
I'd start by reading BOL (Books Online). If that creates more questions than answers, come back and ask specific questions for clarification.
July 21, 2009 at 2:18 pm
anitha is incorrect, he's confusing the way oracle or other databases handle triggers, vs the way SQL Server works.
SQL Server's trigger is called once for any statement, whether it is one row or a million rows.
in your case, since you need to call a stored proc to send your email, you'd want to do call the procedure one time for each row inserted.
this trigger I'm pasting below would correctly execute for each item that met the criteria; note the one thing missing is the Primary key of the inserted and deleted tables..i assumed "ID" was the column name, but you'd need to change that.
--USE [TRAIN]
GO
/****** Object: Trigger [dbo].[tr_CHECK_SRVSTAT] Script Date: 07/21/2009 09:47:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_CHECK_SRVSTAT]
ON [TRAIN].[dbo].[SVC00200]
AFTER UPDATE
AS
BEGIN --Trigger body
DECLARE @CALLNBR VARCHAR(64)
DECLARE C1 CURSOR FOR
SELECT
INSERTED.CALLNBR
FROM INSERTED
INNER JOIN DELETED
ON INSERTED.ID = DELETED.ID
WHERE INSERTED.SRVSTAT DELETED.SRVSTAT --had to change from previous value
AND INSERTED.SRVSTAT= 800 --changed to 800
OPEN C1
FETCH NEXT FROM C1 INTO @CALLNBR
WHILE @@fetch_status -1
BEGIN
EXEC sp_webServiceRequest_EmailComplete @CALLNBR
FETCH NEXT FROM C1 INTO @CALLNBR
END
CLOSE C1
DEALLOCATE C1
END--Trigger body
Lowell
July 21, 2009 at 5:44 pm
I'd sure NOT put anything in a trigger that has to do with actual emailing or any other form of RBAR unless you like getting phone calls about why the server is so slow. The most I'd do is have it fill another table with the information in a set based fashion and let a separate email "crawler" proc handle sending the emails.
If you don't think so, consider what happens if the email server goes down. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply