| Author |
 Topic  |
|
|
kyleb
Starting Member
USA
7 Posts |
 Posted - 10/15/2012 : 12:20:38
|
Hi, there may be a better way to do this, help is appreciated. Right now, the following trigger below does execute if the "if exists" command is true, but no other inserts are firing if it is false. I want what would have inserted by default to fire if false. I am guessing I could do some type of ELSE statement to effectively INSERT via SELECT * FROM INSERTED but wasn't sure if that was necessary or if there was a better way to do this?
-----------------------------------------------------------
CREATE TRIGGER [dbo].[ExclCCP]
ON [dbo].[EmailQueue]
INSTEAD OF INSERT
AS
IF EXISTS (Select MessageType from Inserted where MessageType=22
and EmailAddress in (Select Email from Customers Where SourceID=89))
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO EMAILQUEUE
(OtherData)
SELECT EmailAddress
FROM INSERTED
WHERE MessageType=22
END
-----------------------------------------------------------
PS -- it appears to make this work I really need to stop the insert from occurring and I can't do an UPDATE after the insert or email my email message has already fired.
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/15/2012 : 14:29:43
|
| After reading your posting couple of times, the logic that you are trying to implement is not clear to me. Assume that you have a few rows that you want to insert via a single insert statement. Soem have MessageType=22, some have other MessageTypes; among those with MessageType=22, some may exist in Customers where sourceId=89, and some may not. Can you describe what you want to happen in each of those cases? |
 |
|
|
kyleb
Starting Member
USA
7 Posts |
Posted - 10/15/2012 : 15:10:19
|
Thanks for your reply. We have certain customers coming from a particular referral source (sourceID 89) that we do NOT want to send a welcome email to (message type 22). We do not have the ability to change the underlying DLL/Visual Basic program that sends an email to all new customers, and thus are reverting to a trigger to get around our issue.
If it does not meet this criteria, we want it do what it would have done had we not inserted the trigger. This would include all other message types (such as order receipts) for any sourceID (including 89).
I have modified the code to do what you see below and it all seems to work except for the message type 22's that are not sourceID 89 (those are excluding from sending an email too). Here's how code currently stands:
CREATE TRIGGER [dbo].[ExclCCP]
ON [dbo].[EmailQueue]
INSTEAD OF INSERT
AS
IF EXISTS (Select MessageType from Inserted where MessageType=22
and EmailAddress in (Select Email from Customers Where SourceID=89))
BEGIN
-- Insert statements for procedure here
INSERT INTO EMAILQUEUE
(OtherData)
SELECT EmailAddress
FROM INSERTED
WHERE MessageType=22
END
ELSE
BEGIN
-- Insert statements for procedure here
INSERT INTO EMAILQUEUE
(NAFILL,Status,CompanyID,MessageID,OrderID,RecordID,OrderRestaurantID,DateTimeIn,DateTimeSent,
EmailAddress,EmailName,OperatorID,OtherData,MessageType)
SELECT
NAFILL,Status,CompanyID,MessageID,OrderID,RecordID,OrderRestaurantID,DateTimeIn,DateTimeSent,
EmailAddress,EmailName,OperatorID,OtherData,MessageType
FROM Inserted
END |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 10/15/2012 : 22:15:38
|
sounds like the below
CREATE TRIGGER [dbo].[ExclCCP]
ON [dbo].[EmailQueue]
INSTEAD OF INSERT
AS
IF EXISTS (Select MessageType from Inserted where MessageType=22
and EmailAddress in (Select Email from Customers Where SourceID=89))
BEGIN
-- Insert statements for procedure here
INSERT INTO EMAILQUEUE
(OtherData)
SELECT EmailAddress
FROM INSERTED i
INNER JOIN Customers c
ON c.Email = i.EmailAddress
WHERE MessageType=22
AND c.SourceID = 89
END
ELSE
BEGIN
-- Insert statements for procedure here
INSERT INTO EMAILQUEUE
(NAFILL,Status,CompanyID,MessageID,OrderID,RecordID,OrderRestaurantID,DateTimeIn,DateTimeSent,
EmailAddress,EmailName,OperatorID,OtherData,MessageType)
SELECT
NAFILL,Status,CompanyID,MessageID,OrderID,RecordID,OrderRestaurantID,DateTimeIn,DateTimeSent,
EmailAddress,EmailName,OperatorID,OtherData,MessageType
FROM Inserted
END
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
kyleb
Starting Member
USA
7 Posts |
Posted - 10/16/2012 : 01:29:59
|
| Thank you will give this a try and report back. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 10/16/2012 : 09:16:01
|
welcome
will wait for your reply
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Instead of Insert Trigger Question
