CODEWITHSUNDEEP
sqlsql-serverstored-procedurestriggers
Azfar Kashif
Azfar Kashif

Reputation: 193

Insert trigger that compares values and sends mail

Currently, I've tested this trigger that sends me an email at every insert in the table.

CREATE TRIGGER dr_insert ON data_record 
    AFTER INSERT 
AS 

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'TEST',
        @recipients = '[email protected]',
        @body = 'New data uploaded',
        @subject = 'TriggerTest';

GO

How do I customize this trigger such that it considers a column from the newly inserted row, compares it (e.g. temp > 70 || temp <40) and if the condition matches, THEN sends me the mail?

Upvotes: 1

Views: 73

Answers (1)

Nikhil Vartak
Nikhil Vartak

Reputation: 5117

You need to use inserted table. It is also called a magic table in SQL.

CREATE TRIGGER dr_insert ON data_record 
    AFTER INSERT 
AS 
BEGIN
    DECLARE @temp INT

    SET @temp = (SELECT column FROM inserted)

    IF @temp > 70 || @temp <40
        BEGIN
            EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'TEST',
            @recipients = '[email protected]',
            @body = 'New data uploaded',
            @subject = 'TriggerTest';
        END
END
GO

Upvotes: 1

Related Questions