CODEWITHSUNDEEP
sql-servertriggers
Othman kun
Othman kun

Reputation: 47

SQL Server 2000 trigger on insert

I need to create a trigger for my table that will contain millions of inserts.

That trigger will send an email (already done) to me saying that table is being powered.

That email should be send only once when the insert starts (one a day).

In other words, I wanna do this :

Create trigger on table T1 on FIRST INSERT; EXEC my procedure to send email

Not being a developer I really don't know how to write this...

Thank you for you help.

Upvotes: 1

Views: 1874

Answers (3)

muhmud
muhmud

Reputation: 4604

Why not have a table that you also update in your trigger with the last time that an email was sent out? Then you could query this table in the trigger and decide whether you want to send out another on

create trigger tr
on tab after insert
as
begin
    declare @today datetime;
    set @today = cast(convert(varchar(32), getdate(), 112) as datetime);

    if not exists(select * from logtab where logdate = @today)
    begin
        -- Send the email
        exec sendMail

        -- Update Log Table
        update logtab
            set logdate = @today
    end
end
GO

Upvotes: 1

marc_s
marc_s

Reputation: 754388

You cannot easily do a ON FIRST INSERT trigger.

What you could do is this:

  • create a normal ON INSERT trigger that sends you the email

  • at the end of that trigger, disable that trigger:

    ALTER TABLE dbo.YourTable DISABLE TRIGGER YourTriggerNameHere

This would prevent the trigger from firing over and over again and sending you tons of e-mail.

Then, you'd also need a SQL Agent job that would at night enable that trigger again - so that it could fire again for the first insert of the next day.

Update: OK, so in your case it would be:

CREATE TRIGGER sendMail 
ON MyTable AFTER INSERT 
AS 
    EXEC master.dbo.MyProcedure 

    ALTER TABLE dbo.MyTable DISABLE TRIGGER sendMail

and in your SQL Agent job at night, you'd need:

ALTER TABLE dbo.MyTable ENABLE TRIGGER sendMail

Upvotes: 1

Luis Tellez
Luis Tellez

Reputation: 2973

You cant directly fire a trigger on first update.

You can however have a table that log when you send emails and for what table, and in the trigger you can ask if there is a record there for that day dont send it, if its not, insert in that table and call your sp.

Your sp could do this check also.

Upvotes: 0

Related Questions