日期:2014-05-17  浏览次数:20742 次

sql server触发器转换为Oracle,谢谢!
create TRIGGER trig_dev
ON TAB_GPSDEV
FOR INSERT,update,delete
NOT FOR REPLICATION  
AS
  BEGIN  
  set NOCOUNT ON
  begin  
  insert into tab_user_dev (user_id,dev_id,chg_action) 
  select a.user_id,a.dev_id,0 from 
  (select c.user_id,d.dev_id from tab_user_cargroup c 
  inner join deleted d on c.group_id = d.group_id
  inner join tab_users u1 on c.user_id = u1.user_id
  where u1.user_stat = 0) a
  left outer join
  (select c.user_id,i.dev_id from tab_user_cargroup c 
  INNER JOIN inserted i on c.group_id = i.group_id
  inner join tab_users u1 on c.user_id = u1.user_id
  where u1.user_stat = 0) b
  on a.user_id = b.user_id and a.dev_id = b.dev_id where isnull(b.user_id,-1)=-1  
  end
  begin
  insert into tab_user_dev (user_id,dev_id,chg_action) 
  select a.user_id,a.dev_id,1 from 
  (select c.user_id,i.dev_id from tab_user_cargroup c 
  inner join inserted i on c.group_id = i.group_id
  inner join tab_users u1 on c.user_id = u1.user_id
  where u1.user_stat = 0) a
  left outer join
  (select c.user_id,d.dev_id from tab_user_cargroup c 
  INNER JOIN deleted d on c.group_id = d.group_id
  inner join tab_users u1 on c.user_id = u1.user_id
  where u1.user_stat = 0) b
  on a.user_id = b.user_id and a.dev_id = b.dev_id where isnull(b.user_id,-1)=-1  
  end 
  if update(device_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
  inner join tab_users u1 on a.user_id = u1.user_id
  where u1.user_stat = 0 and 
  c.dev_id in (select dev_id from inserted where device_stat = 1) 
  end  
  if update(device_stat)
  begin
  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
  inner join tab_users u1 on a.user_id = u1.user_id
  where u1.user_stat = 0 and 
  c.dev_id in (select dev_id from inserted where device_stat = 0) 
  end
  SET NOCOUNT OFF
  END

------解决方案--------------------
create TRIGGER trig_dev BEFORE INSERT,UPDATE,DELETE
ON TAB_GPSDEV 
FOR EACH ROW
AS 
BEGIN
IF INSERTING THEN
insert into tab_user_dev (user_id,dev_id,chg_action) 
select a.user_id,a.dev_id,0 from 
(select c.user_id,d.dev_id from tab_user_cargroup c 
inner join deleted d on c.group_id = d.group_id 
inner join tab_users u1 on c.user_id = u1.user_id 
where u1.user_stat = 0) a 
left outer join 
(select c.user_id,i.dev_id from tab_user_cargroup c 
INNER JOIN inserted i on c.group_id = i.group_id