TRIGGERS

  • CREATE TRIGGER CustomerInsert

    ON dbo.CustomerInfo

    FOR insert

    AS

    declare @body varchar(2000)

    declare @CustomerNewName varchar(10)

    SELECT @CustomerNewName = First_Name

    FROM inserted where last_name='ZZZZZZZZZZ'

    SET @body = 'customerid inserted is'+ @CustomerNewName

    EXEC master..xp_sendmail

    @recipients = 'xxxxx@yyyyy.com',

    @subject = 'Customer Information Updated',

    @message = @body

    This trigger is invoked each time any new row is inserted. I need a trigger which is invoked only if the where clause condition is satisfied. And also I have table populated with 10000 rows . There are rows where the condition in the trigger is satisfied. But I need the trigger to be invoked only for the new rows which will be inserted on not on the old rows already present in the table .

    Thanks

  • The trigger will always fire, and will only fire, when an INSERT takes place. You will have to put logic within the trigger to test for the other conditions, and determine what action(s) to take.

    If you wanted to retroactively process existing rows, you would need to create a separate query or stored procedure to process them.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Thats where I guess I need some assistance . Appreciate your response .

    Thanks

  • Here is an example that just logs the first names instead of emailing them, but you should get the idea.

    You didn't mention whether or not you might ever do an INSERT that inserts multiple rows. If you do, your processing logic would have to change to store all the inserted rows and (sigh) execute a loop to email them one at a time. I personally would rather see one email per INSERT, and have the body contain the list of 'ZZZZZZZZZ' names that were inserted. The example below illustrates how names can slip past the trigger, if multiple rows are added with a single INSERT.

    use Sandbox;

    if object_id(N'dbo.TriggerTest') is not null DROP TABLE dbo.TriggerTest;

    create table dbo.TriggerTest (ID int identity(1,1), First_Name varchar(20), Last_Name varchar(20));

    if object_id(N'dbo.TriggerLog') is not null DROP TABLE dbo.TriggerLog;

    create table dbo.TriggerLog (ID int identity(1,1), body varchar(100));

    insert into dbo.Triggertest

    select 'Aaron','Adams' union all

    select 'Buddy','Baker' union all

    select 'Charles','Carver'

    -- select * from dbo.TriggerTest=

    -- everything above is just to set the stage

    GO

    CREATE Trigger dbo.CustomerInsert on dbo.TriggerTest for INSERT

    as

    declare @body varchar(2000)

    declare @CustomerNewName varchar(10)

    declare @rc int

    SELECT @CustomerNewName = First_Name

    FROM inserted where last_name='ZZZZZZZZZZ'

    SET @rc = @@ROWCOUNT

    -------------------------------------------------------------------------------------------------

    -- if a row is returned from the statement above, then execute everything between BEGIN and END

    -------------------------------------------------------------------------------------------------

    IF @rc > 0

    BEGIN

    SET @body = 'first name is '+ @CustomerNewName

    insert into dbo.TriggerLog

    select @body

    END

    GO

    ------- test the trigger

    insert into dbo.TriggerTest

    select 'Zipper','ZZZZZZZZZZ'

    insert into dbo.TriggerTest

    select 'Edgar','Evans'

    -- if multiple-row inserts are possible, ZAPPER and ZOEY are going to get lost under the current logic

    insert into dbo.TriggerTest

    select 'Zinger','ZZZZZZZZZZ' union all

    select 'Fred','Farnsworth' union all

    select 'Zapper','ZZZZZZZZZZ' union all

    select 'Zoey','ZZZZZZZZZZ'

    select * from dbo.TriggerTest

    select * from dbo.TriggerLog

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Thanks Mate..that helped !!

  • You're welcome, and thanks for the feedback. Good luck to you.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply