April 22, 2008 at 3:09 pm
Hi
I want to combine both the triggers into One trigger, can some one help me on this.
ALTER TRIGGER [dbo].[Trigger1] ON [dbo].[SVC00201]
FOR INSERT
AS
DECLARE
@callnbr varchar(11),
@custname varchar(65),
@serialnumber varchar(31)
SELECT *
FROM inserted INNER JOIN
dbo.SVC00200 ON inserted.CALLNBR = dbo.SVC00200.CALLNBR AND inserted.SRVRECTYPE = dbo.SVC00200.SRVRECTYPE INNER JOIN
dbo.SVC00300 ON inserted.EQUIPID = dbo.SVC00300.EQUIPID
WHERE ((LTRIM(RTRIM(inserted.ITEMNMBR)) = 'GCK-10') AND inserted.SRVRECTYPE = 2
if @@ROWCOUNT =1
BEGIN
--- send out email
SELECT @callnbr = LTRIM(RTRIM(inserted.CALLNBR)),
@custname = LTRIM(RTRIM(dbo.SVC00200.CUSTNAME)),
@serialnumber = LTRIM(RTRIM(dbo.SVC00300.SERLNMBR))
FROM inserted INNER JOIN
dbo.SVC00200 ON inserted.CALLNBR = dbo.SVC00200.CALLNBR AND inserted.SRVRECTYPE = dbo.SVC00200.SRVRECTYPE INNER JOIN
dbo.SVC00300 ON inserted.EQUIPID = dbo.SVC00300.EQUIPID
WHERE ((LTRIM(RTRIM(inserted.ITEMNMBR)) = 'GCK-10')
AND inserted.SRVRECTYPE = 2
SET @body = 'SERIAL NUMBER: ' + @serialnumber + CHAR(13) +
'CALL NUMBER: ' + @callnbr + CHAR(13) +
'CUSTOMER: ' + @custname + CHAR(13)
Exec master.dbo.xp_sendmail @recipients = 'firstemail@xyz.com', @message=@body, @subject = 'GCK-10 has been added'
END
ALTER TRIGGER [dbo].[Trigger2] ON [dbo].[SVC00201]
FOR INSERT
AS
DECLARE
@callnbr varchar(11),
@custname varchar(65),
@serialnumber varchar(31)
SELECT *
FROM inserted INNER JOIN
dbo.SVC00200 ON inserted.CALLNBR = dbo.SVC00200.CALLNBR AND inserted.SRVRECTYPE = dbo.SVC00200.SRVRECTYPE INNER JOIN
dbo.SVC00300 ON inserted.EQUIPID = dbo.SVC00300.EQUIPID
WHERE ((LTRIM(RTRIM(inserted.ITEMNMBR)) = 'RBU') AND inserted.SRVRECTYPE = 2
if @@ROWCOUNT =1
BEGIN
--- send out email
SELECT @callnbr = LTRIM(RTRIM(inserted.CALLNBR)),
@custname = LTRIM(RTRIM(dbo.SVC00200.CUSTNAME)),
@serialnumber = LTRIM(RTRIM(dbo.SVC00300.SERLNMBR))
FROM inserted INNER JOIN
dbo.SVC00200 ON inserted.CALLNBR = dbo.SVC00200.CALLNBR AND inserted.SRVRECTYPE = dbo.SVC00200.SRVRECTYPE INNER JOIN
dbo.SVC00300 ON inserted.EQUIPID = dbo.SVC00300.EQUIPID
WHERE ((LTRIM(RTRIM(inserted.ITEMNMBR)) = 'RBU' AND inserted.SRVRECTYPE = 2
SET @body = 'SERIAL NUMBER: ' + @serialnumber + CHAR(13) +
'CALL NUMBER: ' + @callnbr + CHAR(13) +
'CUSTOMER: ' + @custname + CHAR(13)
Exec master.dbo.xp_sendmail @recipients = 'secondmail@xyz.com', @message=@body, @subject = 'RBU has been added'
END
----------
difference is in both triggers is where condition, depend on condition email should go to different email id's.
Thank you,
Regards
vijji
April 23, 2008 at 3:45 am
Are you aware of the fact that triggers fire *per statement* and *not per row*? Your will get unexpected results for multi-row inserts.
Instead of using the SELECT query (which actually returns data to the caller) use the EXISTS operator in an IF statement followed by a BEGIN...END section containing the logic to send mail.
As far as I can see (you haven't posted table DDL or sample data) you can safely combine both queries (i.e. IF blocks) inside a single trigger. Just put them one after another.
For a more accurate answer post DDL, sample data and expected results.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
April 24, 2008 at 12:38 pm
I agree that you have to be very careful when assigning variables to something that might have more than 1 row in it. It's a bad practice. To avoid that and combine the triggers you might want to do something like:
ALTER TRIGGER [dbo].[BothTriggers] ON [dbo].[SVC00201]
FOR INSERT
AS
BEGIN
DECLARE
@callnbr varchar(11),
@custname varchar(65),
@serialnumber varchar(31)
SELECT
LTRIM(RTRIM(I.CALLNBR)) AS CALLNBR
, LTRIM(RTRIM(S2.CUSTNAME)) AS CUSTNAME
, LTRIM(RTRIM(S3.SERLNMBR)) AS SERLNMR
INTO #T1
FROM
inserted I
INNER JOIN
dbo.SVC00200 S2 ON
I.CALLNBR = S2.CALLNBR
AND I.SRVRECTYPE = S2.SRVRECTYPE
INNER JOIN
dbo.SVC00300 S3 ON
I.EQUIPID = S3.EQUIPID
WHERE
((LTRIM(RTRIM(I.ITEMNMBR)) = 'RBU')
AND I.SRVRECTYPE = 2
IF @@ROWCOUNT <> 1
GOTO CheckTheOtherCondition
--- send out email
SELECT @callnbr = CALLNBR
, @custname = CUSTNAME
, @serialnumber = SERLNMBR
FROM #T1
SET @body = 'SERIAL NUMBER: ' + @serialnumber + CHAR(13) +
'CALL NUMBER: ' + @callnbr + CHAR(13) +
'CUSTOMER: ' + @custname + CHAR(13)
Exec master.dbo.xp_sendmail @recipients = 'secondmail@xyz.com', @message=@body, @subject = 'RBU has been added'
CheckTheOtherCondition:
--
DROP TABLE #T1
DROP TABLE #T2
RETURN
END
Todd Fifield
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply