SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Instead of Insert Trigger Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kyleb
Starting Member

USA
7 Posts

Posted - 10/15/2012 :  12:20:38  Show Profile  Reply with Quote
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

5155 Posts

Posted - 10/15/2012 :  14:29:43  Show Profile  Reply with Quote
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?
Go to Top of Page

kyleb
Starting Member

USA
7 Posts

Posted - 10/15/2012 :  15:10:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/15/2012 :  22:15:38  Show Profile  Reply with Quote
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/

Go to Top of Page

kyleb
Starting Member

USA
7 Posts

Posted - 10/16/2012 :  01:29:59  Show Profile  Reply with Quote
Thank you will give this a try and report back.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/16/2012 :  09:16:01  Show Profile  Reply with Quote
welcome

will wait for your reply

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000