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
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

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

elsif updating 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
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)

elsif deleting then
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 if;
END
[解决办法]

SQL code

CREATE OR REPLACE TRIGGER TRIG_DEV  BEFORE INSERT OR DELETE OR UPDATE OF DEVICE_STAT ON TAB_GPSDEV  FOR EACH ROWBEGIN  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;  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;  IF UPDATING 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       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;  IF UPDATING THEN    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 IF;END;/