当表contact中有数据插入时,把插入时间和id复制到对应的sync_log日志表中。当表内有此id时执行更新,没有此id则插入。
不使用merge into语句的触发器可以正常工作,但使用了merge into语句的无法工作。请问我的merge语句存在什么错误。
具体触发器分别如下:
--不使用merge into语句(正常工作):
CREATE OR REPLACE TRIGGER trigger_on_contact AFTER INSERT ON contact FOR EACH ROW
DECLARE C_USER VARCHAR(20);
BEGIN
SELECT SYS_CONTEXT('userenv','session_user') into C_USER FROM DUAL;
IF C_USER != 'SCOTT' THEN
update sync_log
SET
type_field = 'N',
time_field = CURRENT_TIMESTAMP
WHERE source_field = 'contact' AND key_field=:NEW.ID_CONTACT
AND EXISTS(select 1 from sync_log where source_field = 'contact' AND key_field = :NEW.ID_CONTACT);
INSERT INTO sync_log (source_field,key_field,type_field,time_field)values('contact',:NEW.ID_CONTACT,'N',CURRENT_TIMESTAMP);
END IF;
END;--使用merge into语句如下(可以建立触发器,但触发时报错):
CREATE OR REPLACE TRIGGER trigger_on_contact AFTER INSERT ON contact FOR EACH ROW
DECLARE C_USER VARCHAR(20);
BEGIN
SELECT SYS_CONTEXT('userenv','session_user') into C_USER FROM DUAL;
IF C_USER != 'SCOTT' THEN
MERGE INTO SYNC_LOG L
USING(SELECT ID_CONTACT FROM contact) T
ON (L.key_field=T.ID_CONTACT and L.key_field=:NEW.ID_CONTACT and L.source_field='contact')
WHEN MATCHED THEN UPDATE SET L.type_field = 'N',L.time_field = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (L.source_field,L.key_field,L.type_field,L.time_field)values('contact',:NEW.ID_CONTACT,'N',CURRENT_TIMESTAMP);
END IF;
END;
不使用merge into语句的触发器可以正常工作,但使用了merge into语句的无法工作。请问我的merge语句存在什么错误。
具体触发器分别如下:
--不使用merge into语句(正常工作):
CREATE OR REPLACE TRIGGER trigger_on_contact AFTER INSERT ON contact FOR EACH ROW
DECLARE C_USER VARCHAR(20);
BEGIN
SELECT SYS_CONTEXT('userenv','session_user') into C_USER FROM DUAL;
IF C_USER != 'SCOTT' THEN
update sync_log
SET
type_field = 'N',
time_field = CURRENT_TIMESTAMP
WHERE source_field = 'contact' AND key_field=:NEW.ID_CONTACT
AND EXISTS(select 1 from sync_log where source_field = 'contact' AND key_field = :NEW.ID_CONTACT);
INSERT INTO sync_log (source_field,key_field,type_field,time_field)values('contact',:NEW.ID_CONTACT,'N',CURRENT_TIMESTAMP);
END IF;
END;--使用merge into语句如下(可以建立触发器,但触发时报错):
CREATE OR REPLACE TRIGGER trigger_on_contact AFTER INSERT ON contact FOR EACH ROW
DECLARE C_USER VARCHAR(20);
BEGIN
SELECT SYS_CONTEXT('userenv','session_user') into C_USER FROM DUAL;
IF C_USER != 'SCOTT' THEN
MERGE INTO SYNC_LOG L
USING(SELECT ID_CONTACT FROM contact) T
ON (L.key_field=T.ID_CONTACT and L.key_field=:NEW.ID_CONTACT and L.source_field='contact')
WHEN MATCHED THEN UPDATE SET L.type_field = 'N',L.time_field = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (L.source_field,L.key_field,L.type_field,L.time_field)values('contact',:NEW.ID_CONTACT,'N',CURRENT_TIMESTAMP);
END IF;
END;
解决方案 »
- 在linux安装oracle 11G时,在输入EMAIL界面就不动了
- 急急急 冷恢复后用户信息出错
- Warning: Procedure created with compilation errors
- 请教一个特别的分组查询语句问题
- 请求调优,我有个20万的表,每次做简单的count查询,都要花2,3秒钟,有什么办法调优吗?
- 一个oracle的触发器,改成sqlserver应该怎么写?谢谢
- 一个SQL语句取值的问题,大家帮忙看看。。。。
- 用sql 语句怎么使oracle的序列增加1?
- 怎样设置让oracle9i启动的时候不启动web server?
- oracle数据库如何导出大于某个日期整个库的数据
- 逗号分隔字段取前三项
- 求助将SQLServer触发器中的一段代码翻译成Oracle中的代码
CREATE OR REPLACE TRIGGER trigger_on_contact AFTER INSERT ON scott.contact FOR EACH ROW
DECLARE C_USER VARCHAR(20);
BEGIN
SELECT SYS_CONTEXT('userenv','session_user') into C_USER FROM DUAL;
IF C_USER != 'SCOTT' THEN
MERGE INTO SCOTT.SYNC_LOG s
USING DUAL
ON (dual.dummy is not null and s.key_field=:NEW.ID_CONTACT and s.source_field='contact')
WHEN MATCHED THEN UPDATE SET s.type_field = 'N',s.time_field = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (s.key_field,s.source_field,s.type_field,s.time_field)values(:NEW.ID_CONTACT,'contact','N',CURRENT_TIMESTAMP);
END IF;
END;