'Simple' Trigger question

Transact-SQL
1

Hello SQL Guru's :),

I want to use a trigger to get notified when a column is updated to a certain value.In the notification I want to see - machine - operator - date/time. The notification must only send when the column is update by the value '1'.

I searched already on the net but have not found the right answers and as I have no experience with triggers I'm a little bit scary to do something I 'm not sure of.
Thank you in advance.

Kind regards,
Kees

2

hope this helps .. please let me know :slight_smile::slight_smile:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

image
image.png2033×562 88.2 KB

UPDATE()

when update happens .. before after values are stored in
deleted inserted system tables

deleted system table before update values
inserted system table "after update" values

select machine - operator - date/time when inserted.value = 1

notification must only send
how send ???

example
put notification values......... into table
or
desktop alert ......... notification values

3

I'm assuming by "notification" you mean email. Something like below should do it:

CREATE TRIGGER trigger_name
ON dbo.table_name
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(a_column)
BEGIN
    DECLARE @email_commands nvarchar(max)
    SELECT @email_commands = ((SELECT ';EXEC msdb..sp_send_dbmail <your_email_params_here>'
    FROM inserted i
    WHERE i.a_column = 'certain_value'
    FOR XML PATH(''), TYPE))
    EXEC(@email_commmands)
END /*IF*/
/*end of trigger*/
1 Like