create trigger trig_user_update
on tab_users
for update
NOT FOR REPLICATION
as
begin
SET NOCOUNT ON
if update(user_stat)
begin
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,0 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat <> 0)
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,1 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat = 0)
end
SET NOCOUNT OFF
end
on tab_users
for update
NOT FOR REPLICATION
as
begin
SET NOCOUNT ON
if update(user_stat)
begin
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,0 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat <> 0)
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,1 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat = 0)
end
SET NOCOUNT OFF
end
after update ontab_users
for each row
begin
if updating(user_stat) then
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,0 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat <> 0) ;
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,1 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat = 0) ;
end if;
end;
修改楼上的一点"for each row "create or replace trigger trig_user_update
after update
on tab_users
begin
if updating(user_stat) then
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,0 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat <> 0) ;
insert into tab_user_dev (user_id,dev_id,chg_action)
select a.user_id,c.dev_id,1 from tab_users a
inner join tab_user_cargroup b on a.user_id = b.user_id
inner join tab_gpsdev c on c.group_id = b.group_id
where a.user_id in (select user_id from inserted where user_stat = 0) ;
end if;
end;