首页新闻找找看学习计划

oracle触发器,新增前查询字段问题

0
悬赏园豆:10 [已关闭问题] 关闭于 2018-05-30 10:08
create or replace trigger randResultToNotice after insert or update or delete
on rand_result for each row
declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
    
begin
if inserting then
    insert into notice(ID,ISLOOK,USER_ID,CONTENT,TYPE,MSG_TIME) values(:NEW.ID,NULL,'这里是USER_ID':NEW.HANDLE_RESULT,'01',:NEW.HANDLE_DATE);
elsif updating then 
    update notice set ID=:NEW.ID,UserName=:NEW.UserName,PassWord=:NEW.PassWord,Status=:NEW.Status where id=:OLD.id;
elsif deleting then
    delete from notice where id=:OLD.id;
end if;
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;
复制代码
 RAND_RESULT  
    ID                VARCHAR2(36) NOT NULL,
    CODE              VARCHAR2(20) NULL,
    UNISCID           VARCHAR2(50) NULL,
    REGNO             VARCHAR2(50) NULL,
    HANDLE_ORGAN      VARCHAR2(50) NULL,
    HANDLE_PERSON     VARCHAR2(50) NULL,
    HANDLE_DATE       TIMESTAMP(9) NULL,
    HANDLE_RESULT     VARCHAR2(200) NULL,
    HANDLE_OPINION    VARCHAR2(200) NULL,
    ISLOOK            CHAR(1) NULL


NOTICE 
    ID          VARCHAR2(36) NOT NULL,
    ISLOOK      CHAR(1) NULL,
    USER_ID     VARCHAR2(36) NULL,
    CONTENT     VARCHAR2(1000) NULL,
    TYPE        VARCHAR2(2) NULL,
    MSG_TIME    TIMESTAMP(9) NULL
复制代码

 

我想问下新增这里,notice的USER_ID我需要 拿RAND_RESULT里的regno字段去REG和USERINFO表查询到USER_ID插入进去,我想问下这个是怎么写.....没写过前面带查询的这种。我还想问下,这两个表字段对应也不一样,我写的插入语句中后面的VALUES这些写的对吗?

如梦一场的主页 如梦一场 | 菜鸟二级 | 园豆:228
提问于:2018-05-28 11:40
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册