March 22, 2012 at 8:48 am
Hi,
I am having an issue with a line in my update trigger. What I want the trigger to do is send an email out when ever a row in the database is updated with the word cancelled.
"ALTER TRIGGER [dbo].[case_email_cancelled] ON [dbo].[Cases]
for update
AS
if case_c_insttype from inserted = 'Cancelled'
BEGIN"
This is what I have currently which is returning an error with incorrect syntax near the word from.
Can anyone help me with this?
Cheers,
March 22, 2012 at 9:03 am
any command with a FROM must have a SELECT...
so that's the error you are getting.
To do what you want, I'd probably use EXISTS:
something like htis, maybe?
IF EXISTS(SELECT 1 from inserted WHERE case_c_insttype = 'Cancelled')
BEGIN
END
Lowell
March 22, 2012 at 9:05 am
Thanks for the reply that has completed successfully. I will report back when the trigger runs and whether it works.
Cheers,
March 22, 2012 at 9:12 am
One important point... Do not assume the inserted table contains a single row. It may not. It will contain all the rows affected by the update.
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
March 23, 2012 at 5:48 am
GilaMonster (3/22/2012)
One important point... Do not assume the inserted table contains a single row. It may not. It will contain all the rows affected by the update.
This is an interesting point. I, as a relative beginner of SQL Server and self taught, was under the illusion that a trigger fired for each row changed in any update. I found out to my cost that this was not the case therefore had to use a CURSOR in the Trigger to loop through the "inserted" and "deleted" tables.
No doubt the purists will say that is wrong and I am of course open to any ways around this.
Many thanks to all the experienced, like GilaMonster and others. and not so experienced contributors to this excellent forum. They have certainly helped me out by just reading the posts and responses.
March 23, 2012 at 5:57 am
Fear Naught (3/23/2012)
This is an interesting point. I, as a relative beginner of SQL Server and self taught, was under the illusion that a trigger fired for each row changed in any update. I found out to my cost that this was not the case therefore had to use a CURSOR in the Trigger to loop through the "inserted" and "deleted" tables.No doubt the purists will say that is wrong and I am of course open to any ways around this.
Many thanks to all the experienced, like GilaMonster and others. and not so experienced contributors to this excellent forum. They have certainly helped me out by just reading the posts and responses.
In your example, it depends on if you wanted an email per row sent, or just a notification; if it was just a notification, so someone knew 5 new Properties were just inserted into the RealEstate database or something, one email might be just fine;
here's an example of that i used in another forum post...i use FOR XML to get a single string of one of the important fields to add to the body of the email.
you could also use FOR XML to build all the TR/TD tags for a multi row email so it has multiple columns...it just depedns on the requirement.
CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
GO
ALTER TRIGGER TR_WHATEVER_NOTIFICATIONS
ON WHATEVER FOR INSERT
AS
BEGIN
SET NOCOUNT ON
--gather the information, making sure you get it from the INSERTED virtual table, and not the full table
DECLARE @CAPTUREDSTRING VARCHAR(max)
--In this example i want a comma delimited list of important facts about what was inserted.
--using the xml technique to make my comma delimited string.
SELECT @CAPTUREDSTRING = [CriticalItems]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + DESCRIP
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY DESCRIP
FOR XML PATH('')
),1,1,'') as [CriticalItems]
FROM INSERTED s1
GROUP BY s1.WHATEVERID --- without GROUP BY multiple rows are returned
ORDER BY s1.WHATEVERID) myAlias
--now email the results.
declare @body1 varchar(4000)
set @body1 = 'New Item Notification on the Whatever Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> The following new items were inserted into the table:<P>'
+ @CAPTUREDSTRING
+ '
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default Mail Account',
@recipients='lowell@somedomain.com',
@subject = 'New Item Notification',
@body = @body1,
@body_format = 'HTML'
END --TRIGGER
GO
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
Lowell
March 23, 2012 at 6:48 am
Fear Naught (3/23/2012)
GilaMonster (3/22/2012)
One important point... Do not assume the inserted table contains a single row. It may not. It will contain all the rows affected by the update.This is an interesting point. I, as a relative beginner of SQL Server and self taught, was under the illusion that a trigger fired for each row changed in any update. I found out to my cost that this was not the case therefore had to use a CURSOR in the Trigger to loop through the "inserted" and "deleted" tables.
No doubt the purists will say that is wrong and I am of course open to any ways around this.
Many thanks to all the experienced, like GilaMonster and others. and not so experienced contributors to this excellent forum. They have certainly helped me out by just reading the posts and responses.
Would you consider another design pattern?
What about making your trigger to insert required data into dedicated "notification" table, something like:
-- example of change notification table:
CREATE TABLE MyTable_Changes
(
RowNo BIGINT NOT NULL IDENTITY (1,1)
Change CHAR(1),
ChangeTime DATETIME,
-- All columns as per MyTable
)
-- trigger:
CREATE TRIGGER dbo.t_MyTable_NotifyChange ON dbo.MyTable FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS(SELECT 1 FROM INSERTED)
INSERT dbo.MyTable_Changes SELECT 'D',GETUTCDATE(),* FROM DELETED -- delete
ELSE IF EXISTS (SELECT 1 FROM DELETED)
INSERT dbo.MyTable_Changes SELECT 'U',GETUTCDATE(),* FROM INSERTED -- update
ELSE
INSERT dbo.MyTable_Changes SELECT 'I',GETUTCDATE(),* FROM INSERTED -- insert
END
GO
Then you may have dedicated job which will run with required frequency. This job can read changes in whatever appropriate batches, send email and delete records for which email was send (or, if you want to keep audit record, update "sent" flag (of cause you will need to add it as a column into the above table)) .
This solution can use whatever email technology you have, so you can still use SQL mail or you can run a Windows service on application server or use any other scheduling/job software to do so...
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply