May 27, 2011 at 8:41 am
I am getting the error, "The object 'testtr' does not exist or is invalid for this operation." for the following code. Does anyone know why? The table definitely exists. I just created it......
CREATE TRIGGER ODS_trg
ON testtr
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF
(SELECT end_date FROM DELETED) IS NULL AND (SELECT [status] FROM INSERTED) = 'S'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'test@123.com'
,@subject = 'test subject'
,@body = 'test body'
,@body_format = 'TEXT'
END
END
Mike Scalise, PMP
https://www.michaelscalise.com
May 27, 2011 at 8:49 am
Not on the default schema, maybe? Or accidentally in the wrong database on one or the other connection?
As an aside, the way the trigger is written will throw an error if any transaction tries to update more than one row at a time. Might want to make it fail gracefully, or make it so it can handle multi-row updates, instead of just getting an error.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 27, 2011 at 9:10 am
GSquared (5/27/2011)
Not on the default schema, maybe? Or accidentally in the wrong database on one or the other connection?As an aside, the way the trigger is written will throw an error if any transaction tries to update more than one row at a time. Might want to make it fail gracefully, or make it so it can handle multi-row updates, instead of just getting an error.
It creates successfully now.
funny how "testr" is not the same as "testtr"
Why would it throw an error if a transaction tries to update more than one row? How might I go about making it fail gracefully?
Mike Scalise, PMP
https://www.michaelscalise.com
May 27, 2011 at 9:20 am
This bit will throw an error if there's more than one row:
(SELECT end_date FROM DELETED) IS NULL
AND (SELECT [status] FROM INSERTED) = 'S'
Here's a test I ran:
CREATE TABLE dbo.TestR (
ID INT IDENTITY PRIMARY KEY,
End_Date DATETIME,
[Status] CHAR(1));
GO
CREATE TRIGGER ODS_trg
ON TestR
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF
(SELECT end_date FROM DELETED) IS NULL AND (SELECT [status] FROM INSERTED) = 'S'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'test@123.com'
,@subject = 'test subject'
,@body = 'test body'
,@body_format = 'TEXT'
END
END
GO
INSERT INTO dbo.TestR (End_Date, [Status])
VALUES (GETDATE(), 'A'),(GETDATE(), 'B');
SELECT *
FROM TestR;
UPDATE TestR
SET End_Date = GETDATE();
It gets the expected error, which is:
Msg 512, Level 16, State 1, Procedure ODS_trg, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
You might change it to something that checks for the existence of rows in a join between inserted and deleted that match your criteria.
IF EXISTS
(SELECT *
FROM DELETED
INNER JOIN INSERTED
ON DELETED.ID = INSERTED.ID
WHERE DELETED.End_Date IS NULL
AND INSERTED.[Status] = 'S')
If you do get more than one row, will you need to call sp_sendDBMail once for each one, or just once?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 27, 2011 at 9:30 am
I understand now.
I added a where clause to each query, which should only ever return one row:
SELECT end_date FROM DELETED WHERE ID = '1', etc.
Thanks GSquared!
Mike Scalise, PMP
https://www.michaelscalise.com
May 31, 2011 at 6:37 am
You're welcome.
Are you absolutely possitive that under no circumstances will an update ever be done one more than one row, that you will need to send this e-mail on?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 31, 2011 at 7:15 am
I'm almost 100% certain. Just out of curiosity, what if I said that there might be another row or two? Would you refer me to your previous example?
Mike Scalise, PMP
https://www.michaelscalise.com
May 31, 2011 at 7:21 am
mikes84 (5/31/2011)
I'm almost 100% certain. Just out of curiosity, what if I said that there might be another row or two? Would you refer me to your previous example?
Let me put it this way
NEVER code a trigger to handle only 1 row.
You can't trust luck or future work to protect you against that!
June 1, 2011 at 8:51 am
The question becomes, if more than one row qualifies to fire the trigger, do you send more than one e-mail, or will one e-mail accomplish what you need? What you need to write into the code depend on the business reason for the e-mail.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply