Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger error Expand / Collapse
Author
Message
Posted Tuesday, July 21, 2009 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 2:16 PM
Points: 35, Visits: 99
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

Post #756667
Posted Tuesday, July 21, 2009 9:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #756672
Posted Tuesday, July 21, 2009 9:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 2:16 PM
Points: 35, Visits: 99
Yes, that worked. Any documentation on handling multiples?
Post #756696
Posted Tuesday, July 21, 2009 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 2:20 PM
Points: 3, Visits: 5
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
Post #756891
Posted Tuesday, July 21, 2009 2:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:36 PM
Points: 20,738, Visits: 32,522
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #756944
Posted Tuesday, July 21, 2009 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #756947
Posted Tuesday, July 21, 2009 5:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #757077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse