February 23, 2012 at 8:44 am
Hi Guys
I have this trigger which simply inserts a row of data from one table to another.
However, there is a dependency, only insert the row of data into the other table if the column 'DIALLER DATE' is not null.
The trigger is below
Create TRIGGER out_dialler_calllist ON call_list
AFTER INSERT, update AS
Declare
@AccountNumberint,
@CustomerNumber int,
@WorklistIDint,
@DiallerDatedatetime,
@AccountSeqNumberint,
@DateofRequestdatetime,
@AccountReferencenvarchar,
@TelephoneNumberint,
@Balancedecimal,
@sourcesystemvarchar,
@ProcessdateDatetime,
@statuscodenvarchar,
@statusreasonnvarchar,
@CustomerNamevarchar
SET@DateofRequest = GETDATE()
BEGIN
SELECT @AccountReference=AccountReference, @TelephoneNumber=TelephoneNumber, @Balance=Balance, @CustomerName=CustomerName
FROM INSERTED
INSERT INTO out__calllist(AccountReference, TelephoneNumber, Balance, CustomerName)
VALUES(@AccountReference, @TelephoneNumber, @Balance, @CustomerName)
END
GO
What i was wondering is, how do i include the where clause and how?
Hope you guys can help me, Thanks.
February 23, 2012 at 9:12 am
Your trigger is written under the assumption that there will only ever be one row inserted/updated at a time and you can't guarantee that will always be the case. You need to rewrite this using sets.
Create TRIGGER out_dialler_calllist ON call_list
AFTER INSERT, update AS
BEGIN
INSERT INTO out__calllist(AccountReference, TelephoneNumber, Balance, CustomerName)
SELECT AccountReference, TelephoneNumber, Balance, CustomerName
FROM INSERTED
WHERE DiallerDate IS NOT NULL
END
GO
You've also written a single trigger for inserts and updates. You may want to consider writing separate triggers, because you may want to update the secondary table when the primary table is updated instead of inserting a new record in the secondary table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy