我正在解决家庭作业问题。我的触发器遇到了编译问题,并且无法理解如何创建引用序列和另一个表的触发器,并将这些值插入到我创建的日志表中。我读过,在表列条目上使用 SERIAL not null 可能会消除对序列和引用表的需要 http://www.dbforums.com/informix/1628545-create-sequence-create-trigger.html,但我需要它来完成我的作业。
我需要一些帮助来弄清楚如何让我的触发器进行编译。我的绑定变量 :NEW 存在问题,并且从另一个表插入的 idpay 值也存在问题。我不确定我的声明是否正确以及 BEGIN 语句之后的前两条语句是否正确,因为第一行被标记为编译错误。我认为这个示例出了问题,并且 IF 语句中缺少 NULL 值条目,但不确定将该部分放在编码中的何处。 http://www.razorsql.com/features/oracle_add_sequence_trigger.html
当然还有Oracle 11g 触发器参考文档,我已经看过了,但你也可以自己查看。 http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/triggers.htm#
这是设置问题:
1. 使用捐赠数据库。该组织希望追踪所有质押付款
活动。每次添加、更改或删除质押付款时,都会发生以下情况
信息应捕获在单独的表中:用户名、当前日期、操作
采取(INSERT、UPDATE 或 DELETE)以及付款记录的 idpay 值。
创建一个名为 DD_PAYTRACK 的表来保存此信息。包括主键列
由序列填充,并为该序列创建名为 DD_PTRACK_SEQ 的新序列
主键列。
创建一个触发器来记录所请求的信息以跟踪承诺
付款活动,并测试 idpay=1470 的所有操作类型的触发器。
使用 SELECT 语句显示 DD_PAYTRACK 表中的记录。
select * from dd_paytrack;
IDTRACK PTUSER PTDATE PTACTION PTPAYID
4 AAX5367E220 10-DEC-12 INSERT 1470
5 AAX5367E220 10-DEC-12 UPDATE 1470
6 AAX5367E220 10-DEC-12 DELETE 1470
这是我到目前为止所做的,即使在添加这些插入、更新和删除语句之后,它仍然返回一个空表,但由于下面的答案,我已经修复了建议的内容。
*我修复了它,我必须在编译触发器后添加自己的 Update、Insert 和 Delete 语句。现在效果很好。谢谢诺埃尔!!!*
DROP TABLE dd_paytrack;
DROP SEQUENCE idtrack_seq;
CREATE TABLE dd_paytrack(
idtrack NUMBER,
pt_user VARCHAR2(30),
pt_date DATE,
pt_action VARCHAR2(30),
pt_payid NUMBER(6,0),
CONSTRAINT pk_dd_paytrack PRIMARY KEY (idtrack)
);
CREATE SEQUENCE idtrack_seq;
CREATE OR REPLACE TRIGGER pledge_pay_trigger
AFTER
INSERT OR
UPDATE OR
DELETE
ON dd_payment
FOR EACH ROW
DECLARE
log_action dd_paytrack.pt_action%TYPE;
id_pay dd_paytrack.pt_payid%TYPE;
BEGIN
id_pay := :NEW.idpay;
IF INSERTING THEN
log_action := 'Insert';
ELSIF UPDATING THEN
log_action := 'Update';
ELSIF DELETING THEN
id_pay := :OLD.idpay;
log_action := 'Delete';
ELSE
DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;
INSERT INTO dd_paytrack (idtrack, pt_user, pt_date, pt_action, pt_payid)
VALUES(idtrack_seq.NEXTVAL, USER, TO_CHAR(SYSDATE, 'DD-MON-YY'), log_action,
id_pay);
END pledge_pay_trigger;
/
INSERT INTO dd_payment(idpay, idpledge, payamt, paydate, paymethod)
VALUES (1470, 105, 250, SYSDATE, 'CC');
commit;
UPDATE dd_payment
SET payamt = 25
WHERE idpay = 1470;
commit;
UPDATE dd_payment
SET payamt = 2000
WHERE idpay = 1470;
COMMIT;
DELETE
FROM dd_payment
WHERE idpay = 1470;
COMMIT;
SET serveroutput ON
select * from dd_paytrack;
这是捐赠表方案,它引用了我的作业问题的 dd_paytrack 表。我测试了它,在剪切和粘贴它之后,这个模式似乎工作正常。
-- The DoGood Donor Database
-- A donor, who represents a person or company that has committed to make a donation to
the DoGood organzation.
DROP TABLE dd_donor CASCADE CONSTRAINTS;
DROP TABLE dd_project CASCADE CONSTRAINTS;
DROP TABLE dd_status CASCADE CONSTRAINTS;
DROP TABLE dd_pledge CASCADE CONSTRAINTS;
DROP TABLE dd_payment CASCADE CONSTRAINTS;
CREATE TABLE DD_Donor (
idDonor number(4),
Firstname varchar2(15),
Lastname varchar2(30),
Typecode CHAR(1),
Street varchar2(40),
City varchar2(20),
State char(2),
Zip varchar2(9),
Phone varchar2(10),
Fax varchar2(10),
Email varchar2(25),
News char(1),
dtentered date DEFAULT SYSDATE,
CONSTRAINT donor_id_pk PRIMARY KEY(idDonor) );
CREATE TABLE DD_Project (
idProj number(6),
Projname varchar2(60),
Projstartdate DATE,
Projenddate DATE,
Projfundgoal number(12,2),
ProjCoord varchar2(20),
CONSTRAINT project_id_pk PRIMARY KEY(idProj),
CONSTRAINT project_name_uk UNIQUE (Projname) );
CREATE TABLE DD_Status (
idStatus number(2),
Statusdesc varchar2(15),
CONSTRAINT status_id_pk PRIMARY KEY(idStatus) );
CREATE TABLE DD_Pledge (
idPledge number(5),
idDonor number(4),
Pledgedate DATE,
Pledgeamt number(8,2),
idProj number(5),
idStatus number(2),
Writeoff number(8,2),
paymonths number(3),
Campaign number(4),
Firstpledge char(1),
CONSTRAINT pledge_id_pk PRIMARY KEY(idPledge),
CONSTRAINT pledge_idDonor_fk FOREIGN KEY (idDonor)
REFERENCES dd_donor (idDonor),
CONSTRAINT pledge_idProj_fk FOREIGN KEY (idProj)
REFERENCES dd_project (idProj),
CONSTRAINT pledge_idStatus_fk FOREIGN KEY (idStatus)
REFERENCES dd_status (idStatus));
CREATE TABLE DD_Payment (
idPay number(6),
idPledge number(5),
Payamt number(8,2),
Paydate DATE,
Paymethod char(2),
CONSTRAINT payment_id_pk PRIMARY KEY(idPay),
CONSTRAINT pay_idpledge_fk FOREIGN KEY (idPledge)
REFERENCES dd_pledge (idPledge) );
INSERT INTO dd_donor
VALUES (301, 'Mary', 'Treanor', 'I','243 main St.', 'Norfolk',
'VA','23510',NULL,NULL,'[email protected] /cdn-cgi/l/email-protection','Y','01-SEP-2012');
INSERT INTO dd_donor
VALUES (302, 'Patrick', 'Lee', 'I','11 Hooper St.', 'Norfolk',
'VA','23510','7572115445',NULL,'[email protected] /cdn-cgi/l/email-protection','N','09-SEP-2012');
INSERT INTO dd_donor
VALUES (303, 'Terry', 'Venor', 'I','556 Loop Lane.', 'Chesapeake',
'VA','23320',NULL,NULL,'[email protected] /cdn-cgi/l/email-protection','Y','18-SEP-2012');
INSERT INTO dd_donor
VALUES (304, 'Sherry', 'Pane', 'I','Center Blvd.', 'Virginia Beach',
'VA','23455',NULL,NULL,'[email protected] /cdn-cgi/l/email-protection','Y','21-SEP-2012');
INSERT INTO dd_donor
VALUES (305, 'Thomas', 'Sheer', 'I','66 Train St.', 'Chesapeake',
'VA','23322','7579390022',NULL,'[email protected] /cdn-cgi/l/email-protection','Y','01-MAR-2013');
INSERT INTO dd_donor
VALUES (306, NULL, 'Coastal Developers', 'B','3667 Shore Dr.', 'Virginia Beach',
'VA','23450','8889220004',NULL,'[email protected] /cdn-cgi/l/email-protection','Y','30-SEP-2012');
INSERT INTO dd_donor
VALUES (307, NULL, 'VA Community Org', 'G','689 Bush Dr.', 'Norfolk',
'VA','23513','7578337467','7578337468','[email protected] /cdn-cgi/l/email-protection','Y','03-OCT-2012');
INSERT INTO dd_donor
VALUES (308, 'Betty', 'Konklin', 'I','11 Shark Ln.', 'Virginia Beach',
'VA','23455','7574550087',NULL,'[email protected] /cdn-cgi/l/email-protection','N','04-OCT-2012');
INSERT INTO dd_donor
VALUES (309, 'Jim', 'Tapp', 'I','200 Pine Tree Blvd.', 'Chesapeake',
'VA','23320','',NULL,'','N','08-OCT-2012');
INSERT INTO dd_donor
VALUES (310, NULL, 'Unique Dezigns', 'B','Connect Circle Unit 12', 'Chesapeake',
'VA','23320','7574442121',NULL,'[email protected] /cdn-cgi/l/email-protection','Y','11-SEP-2012');
INSERT INTO dd_project
VALUES (500,'Elders Assistance League', '01-SEP-2012','31-OCT-2012',15000,'Shawn
Hasee');
INSERT INTO dd_project
VALUES (501,'Community food pantry #21 freezer equipment', '01-OCT-2012','31-DEC-
2012',65000,'Shawn Hasee');
INSERT INTO dd_project
VALUES (502,'Lang Scholarship Fund', '01-JAN-2013','01-NOV-2013',100000,'Traci
Brown');
INSERT INTO dd_project
VALUES (503,'Animal shelter Vet Connect Program', '01-DEC-2012','30-MAR-
2013',25000,'Traci Brown');
INSERT INTO dd_project
VALUES (504,'Shelter Share Project 2013', '01-FEB-2013','31-JUL-2013',35000,'Traci
Brown');
INSERT INTO dd_status
VALUES (10,'Open');
INSERT INTO dd_status
VALUES (20,'Complete');
INSERT INTO dd_status
VALUES (30,'Overdue');
INSERT INTO dd_status
VALUES (40,'Closed');
INSERT INTO dd_status
VALUES (50,'Hold');
INSERT INTO dd_pledge
VALUES (100,303,'18-SEP-2012',80,500,20,NULL,0,738,'Y');
INSERT INTO dd_pledge
VALUES (101,304,'21-SEP-2012',35,500,20,NULL,0,738,'Y');
INSERT INTO dd_pledge
VALUES (102,310,'01-OCT-2012',500,501,20,NULL,0,749,'Y');
INSERT INTO dd_pledge
VALUES (103,307,'03-OCT-2012',2000,501,20,NULL,0,749,'N');
INSERT INTO dd_pledge
VALUES (104,308,'04-OCT-2012',240,501,10,NULL,12,749,'Y');
INSERT INTO dd_pledge
VALUES (105,309,'08-OCT-2012',120,501,10,NULL,12,749,'Y');
INSERT INTO dd_pledge
VALUES (106,301,'12-OCT-2012',75,500,20,NULL,0,738,'N');
INSERT INTO dd_pledge
VALUES (107,302,'15-OCT-2012',1200,501,10,NULL,24,749,'Y');
INSERT INTO dd_pledge
VALUES (108,308,'20-JAN-2013',480,503,10,NULL,24,790,'N');
INSERT INTO dd_pledge
VALUES (109,301,'01-FEB-2013',360,503,10,NULL,12,790,'N');
INSERT INTO dd_pledge
VALUES (110,303,'01-MAR-2013',300,504,10,NULL,12,756,'N');
INSERT INTO dd_pledge
VALUES (111,306,'01-MAR-2013',1500,504,20,NULL,0,756,'Y');
INSERT INTO dd_pledge
VALUES (112,309,'16-MAR-2013',240,504,10,NULL,12,756,'N');
INSERT INTO dd_payment
VALUES (1425,100,80,'18-SEP-2012','CC');
INSERT INTO dd_payment
VALUES (1426,101,35,'21-SEP-2012','DC');
INSERT INTO dd_payment
VALUES (1427,102,500,'01-OCT-2012','CH');
INSERT INTO dd_payment
VALUES (1428,103,2000,'03-OCT-2012','CH');
INSERT INTO dd_payment
VALUES (1429,106,75,'12-OCT-2012','CC');
INSERT INTO dd_payment
VALUES (1430,104,20,'01-NOV-2012','CC');
INSERT INTO dd_payment
VALUES (1431,105,10,'01-NOV-2012','CC');
INSERT INTO dd_payment
VALUES (1432,107,50,'01-NOV-2012','CC');
INSERT INTO dd_payment
VALUES (1433,104,20,'01-DEC-2012','CC');
INSERT INTO dd_payment
VALUES (1434,105,10,'01-DEC-2012','CC');
INSERT INTO dd_payment
VALUES (1435,107,50,'01-DEC-2012','CC');
INSERT INTO dd_payment
VALUES (1436,104,20,'01-JAN-2013','CC');
INSERT INTO dd_payment
VALUES (1437,105,10,'01-JAN-2013','CC');
INSERT INTO dd_payment
VALUES (1438,107,50,'01-JAN-2013','CC');
INSERT INTO dd_payment
VALUES (1439,104,20,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1440,105,10,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1441,107,50,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1442,108,20,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1443,109,30,'01-FEB-2013','CC');
INSERT INTO dd_payment
VALUES (1444,104,20,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1445,105,10,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1446,107,50,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1447,108,20,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1448,109,30,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1449,110,25,'01-MAR-2013','CC');
INSERT INTO dd_payment
VALUES (1450,111,1500,'01-MAR-2013','CH');
INSERT INTO dd_payment
VALUES (1451,104,20,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1452,105,10,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1453,107,50,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1454,108,20,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1455,109,30,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1456,110,25,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1457,112,20,'01-APR-2013','CC');
INSERT INTO dd_payment
VALUES (1458,104,20,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1459,105,10,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1460,107,50,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1461,108,20,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1462,109,30,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1463,110,25,'01-MAY-2013','CC');
INSERT INTO dd_payment
VALUES (1464,112,20,'01-MAY-2013','CC');
COMMIT;