SQL Trigger

Question

I have the attached trigger that runs if the tbl_repair_visit.TENANTSATISFACTION = 'Poor' on an update.

The issue i have if we change the engineers name the Engineer column gets updated and the trigger runs again if TENANTSATISFACTION = 'Poor'

How can i set this up to only run if the TENANTSATISFACTION = 'Poor' column is updated and ignor updates on all other columns

ALTER TRIGGER [dbo].[tr_CustomerSatisfactionAlertRepair] 
    ON [dbo].[tbl_repair_visit] 
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    INSERT alertmessagedata (TypeID, Contract, Address, ORDERID, 
       ENGINEERS, Sent, DateAdded)
    SELECT '5', tbl_property.contract, tbl_property.fulladdress, 
      tbl_repair_visit.orderid, tbl_repair_visit.engineer, 
      0, GETDATE()
    FROM TBL_REPAIR_VISIT 
    INNER JOIN
     INSERTED X ON TBL_REPAIR_VISIT.VISITID = X.VISITID 
    INNER JOIN 
     TBL_PROPERTY ON TBL_REPAIR_VISIT.PROPREF = TBL_PROPERTY.PROPREF
    WHERE tbl_repair_visit.TENANTSATISFACTION = 'Poor'
END

Answer 1

In the update trigger, you can check if a column is being updated:

IF UPDATE(TENANTSATISFACTION) BEGIN .... END

Answer 2

I don't think you can specify that. The triggers UPDATE, DELETE and INSERT are fired whenever an UPDATE, DELETE or INSERT (respectably) is performed on the table.

You can do as Mike K. suggested and check if the column that interests you changed or not.

Answer 3

Can't you do something with the nested triggers option ?
(Set that option off, so that triggers do not cause other triggers to be fired)

Or, maybe you could create an INSTEAD OF trigger, instead of an AFTER trigger. Offcourse, then in your INSTEAD OF trigger, you also have to write the UPDATE (or insert) statement that should do the actual update or insert into the table , next to the additional logic.

Answer 4

Left join the record's new version from old version, if the joined table(i.e. inserted) has null value, it means the fields you want to detect changes has changed.

create table family
(
id int not null identity(1,1),
name varchar(100) not null,
age int not null
);


insert into family(name,age) values('Michael', 32);
insert into family(name,age) values('Matthew', 23);



create trigger TrigUpdOnFamily on family
for update
as

    if exists
        (
        select * from deleted 
        left join inserted on inserted.id = deleted.id    

        -- put the fields to detect here...
        and inserted.age = deleted.age
            -- ...detections
        where inserted.age is null) begin    

        -- detect important fields
        print 'age change';

    end
    else begin
        -- ignore non-important fields
        print 'nothing change';
    end;

go


-- possible SqlCommand from .NET
update family set name = 'Michael', age = 20 where id = 1;    

update family set name = 'Mateo', age = 23 where id = 2;

Answer 5

create table Programmer
(
id int not null identity(1,1),
name varchar(100) not null,
status varchar(20)
);


insert into Programmer(name,status) values('Pampers', 'Rich');




create trigger TrigUpdOnProgrammer on Programmer
for update
as

    if exists
        (
        select * from deleted 
        left join inserted on inserted.id = deleted.id    

        -- put the fields to detect here...
        and inserted.status = deleted.status -- detect if changed
        -- ...detections
        where inserted.status is null) 

       -- if changes detected on status, then check if it is Poor.  
       -- don't worry, there's no performance degradation.  SQL AND is short-circuited
       and exists(select * from inserted where status = 'Poor')
    begin    
       print 'status changed';
    end
    else begin
        print 'changes ignored';
    end;

go

-- execute the following on succession, then check the output

update Programmer set status = 'Poor' where id = 1; -- changes detected

update Programmer set status = 'Poor' where id = 1; -- changes ignored

update Programmer set status = 'Rich' where id = 1; -- changes ignored

update Programmer set status = 'Poor' where id = 1; -- changes detected