Oracle SQL触发器,如果同时在一个文件中,则错误,在单独时,编译良好

19

我是Oracle的新手。我通过SQL Developer运行脚本。我尝试制作几个连接的表,并为其创建两个触发器。

CREATE TABLE BIKE(
    ID NUMBER(8) NOT NULL,
    FRONT_WHEEL_ID NUMBER(8),
    BACK_WHEEL_ID NUMBER(8)

);

CREATE TABLE WHEEL(
    ID NUMBER(8) NOT NULL,
    DIAMETER NUMBER(8,2)

);

CREATE TABLE PRODUCTS(
    ID NUMBER(8) NOT NULL,
    PRODUCT_NAME NVARCHAR2(64),
    PRODUCT_COUNT NUMBER(8)
);

CREATE TABLE PRODUCTS_HISTORY(
    ID NUMBER(8) NOT NULL,
    DIAMETER NUMBER(8,2)    
);

-- PK's
ALTER TABLE BIKE
ADD CONSTRAINT BIKE_PK PRIMARY KEY (ID);

ALTER TABLE WHEEL
ADD CONSTRAINT WHEEL_PK PRIMARY KEY (ID);

ALTER TABLE PRODUCTS
ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (ID);

ALTER TABLE PRODUCTS_HISTORY
ADD CONSTRAINT PRODUCTS_HISTORY_PK PRIMARY KEY (ID);

-- FK's
ALTER TABLE BIKE
ADD CONSTRAINT BIKE_FRONT_WHEEL_FK FOREIGN KEY (FRONT_WHEEL_ID)
REFERENCES WHEEL (ID);

ALTER TABLE BIKE
ADD CONSTRAINT BIKE_BACK_WHEEL_FK FOREIGN KEY (BACK_WHEEL_ID)
REFERENCES WHEEL (ID);

-- Simple data
INSERT INTO PRODUCTS (ID, PRODUCT_NAME) VALUES (1, 'Bikes');
INSERT INTO PRODUCTS (ID, PRODUCT_NAME) VALUES (2, 'Wheels');

-- Triggers

CREATE OR REPLACE TRIGGER WHEEL_after_insert
    AFTER INSERT OR DELETE
       ON WHEEL

    DECLARE
       WHEEL_COUNT NUMBER(8);

    BEGIN
    SELECT COUNT(*) INTO WHEEL_COUNT FROM WHEEL;
    UPDATE PRODUCTS SET PRODUCT_COUNT = WHEEL_COUNT WHERE PRODUCT_NAME = 'Wheels';
    END;

CREATE OR REPLACE TRIGGER BIKE_after_insert
    AFTER INSERT OR DELETE
       ON BIKE

    DECLARE
       BIKE_COUNT NUMBER(8);

    BEGIN
    SELECT COUNT(*) INTO BIKE_COUNT FROM BIKE;
    UPDATE PRODUCTS SET PRODUCT_COUNT = BIKE_COUNT WHERE PRODUCT_NAME = 'Bikes';
    END;    

当我整体运行为一个sql脚本时,我得到:

...
1 rows inserted.
TRIGGER WHEEL_AFTER_INSERT compiled
Errors: check compiler log

当我运行除以下内容之外的所有内容时

    CREATE OR REPLACE TRIGGER BIKE_after_insert

    AFTER INSERT OR DELETE
       ON BIKE

    DECLARE
       BIKE_COUNT NUMBER(8);

    BEGIN
    SELECT COUNT(*) INTO BIKE_COUNT FROM BIKE;
    UPDATE PRODUCTS SET PRODUCT_COUNT = BIKE_COUNT WHERE PRODUCT_NAME = 'Bikes';
    END;

然后在单独的工作表中运行以上命令,一切正常。

我要补充什么?我尝试添加COMMIT;和提交工作;触发之间,但没有任何区别。

贾保佑你的帮助

GolezTrol

/如果同一脚本中有多个程序块(包括触发器声明),则应使用正斜杠(进行分隔通常,最好/在每个PL / SQL块之后添加一个您在SQL语句之间不需要它们。

...
CREATE OR REPLACE TRIGGER WHEEL_after_insert
    AFTER INSERT OR DELETE
       ON WHEEL

    DECLARE
       WHEEL_COUNT NUMBER(8);

    BEGIN
    SELECT COUNT(*) INTO WHEEL_COUNT FROM WHEEL;
    UPDATE PRODUCTS SET PRODUCT_COUNT = WHEEL_COUNT WHERE PRODUCT_NAME = 'Wheels';
    END;
/

CREATE OR REPLACE TRIGGER BIKE_after_insert
    AFTER INSERT OR DELETE
       ON BIKE

    DECLARE
       BIKE_COUNT NUMBER(8);

    BEGIN
    SELECT COUNT(*) INTO BIKE_COUNT FROM BIKE;
    UPDATE PRODUCTS SET PRODUCT_COUNT = BIKE_COUNT WHERE PRODUCT_NAME = 'Bikes';
    END; 
/

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章