Instead of Insert Trigger Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Instead of Insert Trigger Question

Author

Topic

kyleb
Starting Member

7 Posts

Posted - 2012-10-15 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 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

7 Posts

Posted - 2012-10-15 : 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

52326 Posts

Posted - 2012-10-15 : 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

7 Posts

Posted - 2012-10-16 : 01:29:59

Thank you will give this a try and report back.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 09:16:01

welcome

will wait for your reply

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources