当前位置:Gxlcms > 数据库问题 > 连续创建多个Oracle触发器失败,单个创建才成功的解决方法

连续创建多个Oracle触发器失败,单个创建才成功的解决方法

时间:2021-07-01 10:21:17 帮助过:23人阅读

.应用信息 drop table app_info cascade constraints; create table APP_INFO ( id NUMBER(9) not null, appid VARCHAR2(255) default NULL, appkey VARCHAR2(255) default NULL, md5_key VARCHAR2(255) default NULL, aes_key VARCHAR2(255) default NULL, permission VARCHAR2(3000) default NULL, email VARCHAR2(255) default NULL, password VARCHAR2(255) default NULL, ras_public_key VARCHAR2(255) default NULL, constraint APP_INFO primary key (id) ); drop sequence app_info_id; create sequence app_info_id increment by 1 start with 1 nomaxvalue nominvalue nocache; CREATE OR REPLACE TRIGGER addAppInfo BEFORE INSERT ON App_INFO FOR EACH ROW BEGIN SELECT app_info_id.nextval INTO :new.id FROM dual; END; ---2.unit信息 drop table unit_info cascade constraints; create table UNIT_INFO ( UNIT_ID NUMBER(9) not null, USERID VARCHAR2(255) default NULL, UNIT_NAME VARCHAR2(128) default NULL, UNIT_LINKMAN VARCHAR2(64) default NULL, ORGANIZATION_CODE VARCHAR2(64) default NULL, constraint UNIT_INFO primary key (UNIT_ID) ); drop sequence unit_info_id; create sequence unit_info_id increment by 1 start with 1 nomaxvalue nominvalue nocache; CREATE OR REPLACE TRIGGER addUnitInfo BEFORE INSERT ON UNIT_INFO FOR EACH ROW BEGIN SELECT unit_info_id.nextval INTO :new.unit_id FROM dual; END;

错误截图如下:

技术分享图片

这种创建是失败的,因为触发器需要先编译,每创建一个触发器,需要以“/”结束,然后才能执行下一个。

正确的做法应该是这样的;

  1. ---<span style="color: #800080">1</span><span style="color: #000000">.应用信息
  2. drop table app_info cascade constraints;
  3. create table APP_INFO
  4. (
  5. id NUMBER(</span><span style="color: #800080">9</span>) not <span style="color: #0000ff">null</span><span style="color: #000000">,
  6. appid VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  7. appkey VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  8. md5_key VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  9. aes_key VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  10. permission VARCHAR2(</span><span style="color: #800080">3000</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  11. email VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  12. password VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  13. ras_public_key VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  14. constraint APP_INFO primary key (id)
  15. );
  16. drop sequence app_info_id;
  17. create sequence app_info_id
  18. increment by </span><span style="color: #800080">1</span><span style="color: #000000">
  19. start with </span><span style="color: #800080">1</span><span style="color: #000000">
  20. nomaxvalue
  21. nominvalue
  22. nocache;
  23. CREATE OR REPLACE TRIGGER addAppInfo BEFORE INSERT ON App_INFO FOR EACH ROW
  24. BEGIN
  25. SELECT app_info_id.nextval INTO :</span><span style="color: #0000ff">new</span><span style="color: #000000">.id FROM dual;
  26. END;
  27. </span>/
  28. ---<span style="color: #800080">2</span><span style="color: #000000">.unit信息
  29. drop table unit_info cascade constraints;
  30. create table UNIT_INFO
  31. (
  32. UNIT_ID NUMBER(</span><span style="color: #800080">9</span>) not <span style="color: #0000ff">null</span><span style="color: #000000">,
  33. USERID VARCHAR2(</span><span style="color: #800080">255</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  34. UNIT_NAME VARCHAR2(</span><span style="color: #800080">128</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  35. UNIT_LINKMAN VARCHAR2(</span><span style="color: #800080">64</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  36. ORGANIZATION_CODE VARCHAR2(</span><span style="color: #800080">64</span>) <span style="color: #0000ff">default</span><span style="color: #000000"> NULL,
  37. constraint UNIT_INFO primary key (UNIT_ID)
  38. );
  39. drop sequence unit_info_id;
  40. create sequence unit_info_id
  41. increment by </span><span style="color: #800080">1</span><span style="color: #000000">
  42. start with </span><span style="color: #800080">1</span><span style="color: #000000">
  43. nomaxvalue
  44. nominvalue
  45. nocache;
  46. CREATE OR REPLACE TRIGGER addUnitInfo BEFORE INSERT ON UNIT_INFO FOR EACH ROW
  47. BEGIN
  48. SELECT unit_info_id.nextval INTO :</span><span style="color: #0000ff">new</span><span style="color: #000000">.unit_id FROM dual;
  49. END;
  50. </span>/

 注意:存储过程也是一样的。

连续创建多个Oracle触发器失败,单个创建才成功的解决方法

标签:pid   word   成功   strong   nocache   har   触发器   default   const   

人气教程排行