Adding a where clause in a trigger

  • 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.

  • 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