我是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;和提交工作;触发之间,但没有任何区别。
贾保佑你的帮助
/
如果同一脚本中有多个程序块(包括触发器声明),则应使用正斜杠()进行分隔。通常,最好/
在每个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] 删除。
我来说两句