Looking for a simple trigger, I haven't written a trigger before, it should be very simple

lyhtfrykln

TA
id mtype mk
1 aa 123
2 bb 123

TB
id ntype Status ts
1 aa 0 123
2 bb 1 123
3 bb 0 10


----------------
Find a trigger, when mk in TA is updated, the corresponding ts in TB is also updated.

The condition for triggering TB is ntype=mtype and Status=0.

---
Such as operating on TA

update TA set mk='999' where mtype='bb'

Then TB should be:
TB
id ntype Status ts
1 aa 0 123
2 bb 1 123
3 bb 0 999

------------Find this trigger

michealhenry

create trigger t_update on ta
for update
as
begin
update tb
set tb.ts = ta.mk
from inserted ta
where tb.ntype=ta.mtype and tb.Status = 0
end
go

cv4bf5d65fd

create trigger auto_update on TB
as
  update TB set ts = mk from inserted a where id = a.id and Status = 0

asurae

CREATE TRIGGER sync_update ON TA
FOR UPDATE
AS
if exists (select 1 from TB where ntype=INSERTED.mtype and Status=0)
    update TB set ts=INSERTED.mk

啊啦是奶牛

create trigger trigger_update on dbo.TA
for update
as
declare @type varchar(10)
declare @ts int
select @type=i.mytype,@ts=i.mk from inserted i
update TB set ts=@ts where ntype=@type and status=0

lyhtfrykln

Thank you!
It's strange that I built a trigger according to the above method, why the trigger is always in the editing state (that is, a pen is displayed there). What can be used normally should be a green + sign!


Check the grammar and show success again. What is the reason?

lyhtfrykln

Why is that trigger always being edited

How to use it to use it?

lancelover

Use EXE to trigger

lancelover

Sorry, it's EXEC

lancelover

IF EXISTS (SELECT name FROM sysobjects
      WHERE name ='AA' AND type ='TR')
   DROP TRIGGER AA

CREATE TRIGGER AA
ON TA
FOR UPDATE
AS
UPDATE TB SET TB.TS=TA.MK
FROM INSERTED A WHERE ID=A.ID AND STATUS = 0