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这些写的对吗?