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
解决方案 »
- oracle中语句的执行顺序,请教大家!
- 求教ORACLE10G安装问题!
- 一个存储过程游标使用的错误,请帮忙看看。
- 怎么建ORCALE 8i和MYSQl之间建立DBLINK
- sp_addlinkedserver链接oracle服务器报ORA-12705(NLS)错?
- 在oracle的sql语句中可以实现从一个db查询另一个db吗?
- 搞定就送分(在线等后,谢谢)
- 一个导入数据表的问题?急!! supershb(phenix)请进.
- varchar2类型最长能存储多大?如果超过最大值该怎么办呢?
- oracle 0 和 null 的区别
- 专门配置一台透明网关服务器连接Sql Server 2K,跟Oracle数据库服务器分开,DBLink也建好了,调用不了,老是提示“TNS: 无法处理服务名”
- ORACLE触发器问题
CREATE OR REPLACE TRIGGER TRIG_USER_UPDATE
BEFORE UPDATE OF USER_STAT ON TAB_USERS
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;
/
转化成什么啦?