Update trigger fire on Specific column change.

Question

Below trigger ,i am using for updating record,issue is that , when i change codeitem value,trigger also get fire ,which should not be,becasue update trigger i just applied on PID,if value get change then trigger get fire,so is this possible

Create table #Probale (Prdno int,Codeitem int,P_Qty int,PID int)
Create table #tbl_PackDetail (PDID int,Prdno int,Codeitem int,Qty int,PID int)


insert into #Probale values(10004,1,1,4545)


insert into #tbl_PackDetail values(10001,10004,1,1,4545)



ALTER TRIGGER [dbo].[SBDeletePackdetail] ON [dbo].[tbl_PckDetail] AFTER Update AS
UPDATE #Probale
SET    PID = 1
FROM   Probale G
JOIN   inserted i ON G.Prdno = i.Prdno
where ( G.PID!=1 or G.PID is not null)

Answer 1

If you have an UPDATE trigger, then the trigger fires on every update, you can not modify this behaviour.

But you can use the trigger function UPDATE to check which column data have been updated.

Answer 2

Hi @Analyst_SQL

I added a query to your trigger code to monitor intermediate processes.

create or alter TRIGGER [dbo].[SBDeletePackdetail] 
ON tbl_PackDetail 
AFTER Update AS
select * FROM Probale G
JOIN inserted i ON G.Prdno = i.Prdno
where ( G.PID!=1 or G.PID is not null);
UPDATE Probale
SET    PID = 1
FROM  Probale G
JOIN inserted i ON G.Prdno = i.Prdno
where ( G.PID!=1 or G.PID is not null)

Next I run the update statement.

update  tbl_PackDetail set Codeitem = 2

It displays a table of intermediate procedures.

On the basis of this intermediate process table, the PID is updated.（There are two PID columns on the table, and you do not need to specify G.PID.）

Finally, check the two original tables.

I guess that's how this trigger works.

I'm not sure if this is what you want, if there are still questions, please leave a message.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.