插入、更新、删除后的 PL/SQL 触发器，将数据输入到带有序列的日志表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16214107/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL trigger for after insert, update, delete that enters Data into Log Table with a Sequence
提问by MeachamRob
I am working on a homework problem. I am running into compilation issues with my trigger and am having trouble understanding how to create a trigger that references a sequence and another table, and insert those values into a log table that I've created. I have read that using SERIAL not null on the Table column entry may eliminates the need for a sequence and referencing table, but I need that for my homework to work.
我正在解决家庭作业问题。我的触发器遇到编译问题，并且无法理解如何创建一个引用序列和另一个表的触发器，并将这些值插入到我创建的日志表中。我已经读到在 Table 列条目上使用 SERIAL not null 可能会消除对序列和引用 table 的需要，但我需要它来让我的家庭作业工作。
I need some help figuring out how to get my trigger to compile. It is having problems with my Bind Variables :NEW from the sequence, and also the idpay value inserted from another Table. I'm not sure if my declarations are correct and the first two statements after the BEGIN statement are correct as that 1st line was flagged as a compilation error. I think something is going on with this example and my lack of the NULL value entry there in the IF statement, but unsure where to put that part in my coding.
我需要一些帮助来弄清楚如何让我的触发器编译。我的绑定变量有问题：序列中的新，以及从另一个表插入的 idpay 值。我不确定我的声明是否正确，并且 BEGIN 语句之后的前两个语句是否正确，因为第一行被标记为编译错误。 我认为这个例子发生了一些事情，而且我在 IF 语句中缺少 NULL 值条目，但不确定在我的编码中将该部分放在哪里。
And of course the Oracle 11g Reference Doc for Triggers, I've gone over it but you can check it out yourself too.
当然还有Oracle 11g Reference Doc for Triggers，我已经看过了，但您也可以自己查看。
Here is the setup question: 1. Use the Donations database. The organization wants to track all pledge payment activity. Each time a pledge payment is added, changed, or removed, the following information should be captured in a separate table: username, current date, action taken (INSERT, UPDATE, or DELETE), and the idpay value for the payment record.
这是设置问题： 1. 使用捐赠数据库。该组织希望跟踪所有质押支付活动。每次添加、更改或删除质押付款时，都应在单独的表中捕获以下信息：用户名、当前日期、采取的操作（插入、更新或删除）以及付款记录的 idpay 值。
Create a table named DD_PAYTRACK to hold this information. Include a primary key column to be populated by a sequence, and create new sequence named DD_PTRACK_SEQ for the primary key column.
创建一个名为 DD_PAYTRACK 的表来保存此信息。包括要由序列填充的主键列，并为主键列创建名为 DD_PTRACK_SEQ 的新序列。
Create a single trigger for recording the requested information to track pledge payment activity, and test the trigger for all action types for idpay=1470. Display the records from the DD_PAYTRACK table by using SELECT statement.
创建单个触发器来记录请求的信息以跟踪质押支付活动，并针对 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
Here is what I've done so far, even after adding these Insert, Update, and Delete statements, it's still returning an Empty table, but because of the answer below I have fixed what was suggested.
这是我到目前为止所做的，即使在添加了这些 Insert、Update 和 Delete 语句之后，它仍然返回一个空表，但由于下面的答案，我已经修复了建议的内容。
*I fixed it, I had to add my own Update, Insert, and Delete statements after compiling the Trigger. Works great now. Thanks Noel!!!*
*我修复了它，我必须在编译触发器后添加我自己的更新、插入和删除语句。现在效果很好。谢谢诺埃尔！！！*
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;
Here is the Donation Table Scheme which it references the dd_paytrack Table for my Homework problem. I tested it and this schema seems to work okay after cutting and pasting it.
这是捐赠表计划，它引用了我的作业问题的 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]','Y','01-SEP-2012');
INSERT INTO dd_donor
VALUES (302, 'Patrick', 'Lee', 'I','11 Hooper St.', 'Norfolk',
'VA','23510','7572115445',NULL,'[email protected]','N','09-SEP-2012');
INSERT INTO dd_donor
VALUES (303, 'Terry', 'Venor', 'I','556 Loop Lane.', 'Chesapeake',
'VA','23320',NULL,NULL,'[email protected]','Y','18-SEP-2012');
INSERT INTO dd_donor
VALUES (304, 'Sherry', 'Pane', 'I','Center Blvd.', 'Virginia Beach',
'VA','23455',NULL,NULL,'[email protected]','Y','21-SEP-2012');
INSERT INTO dd_donor
VALUES (305, 'Thomas', 'Sheer', 'I','66 Train St.', 'Chesapeake',
'VA','23322','7579390022',NULL,'[email protected]','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]','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]','Y','03-OCT-2012');
INSERT INTO dd_donor
VALUES (308, 'Betty', 'Konklin', 'I','11 Shark Ln.', 'Virginia Beach',
'VA','23455','7574550087',NULL,'[email protected]','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]','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;
采纳答案by Noel
Since you are using the sequence value in the insert statement,
由于您在插入语句中使用序列值，
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
);
there is no need to select it as below.
没有必要选择它如下。
SELECT idtrack_seq.NEXTVAL
INTO :NEW.idtrack
FROM DUAL;
Also, you have missed a colon in this line,
此外，您在这一行中遗漏了一个冒号，
id_pay := :OLD.idpay;
EDIT: One more thing, there is no point in having DBMS_OUTPUT statement in the trigger. Since you wont see it, even if the execution reaches it.
编辑：还有一件事，在触发器中使用 DBMS_OUTPUT 语句是没有意义的。因为你不会看到它，即使执行到达它。
回答by VISHAL KHALASI
PL/SQL trigger for after insert, update, delete that enters Data into Log Table with a Sequence
插入、更新、删除后的 PL/SQL 触发器，将数据输入到带有序列的日志表中
CREATE OR REPLACE TRIGGER pledge_pay_trigger2
AFTER
INSERT OR
UPDATE OR
DELETE
ON dd_payment
FOR EACH ROW
DECLARE
log_action dd_payment2.pt_action%TYPE;
id_pay dd_payment2.idpay%TYPE;
id_Pledge dd_payment2.idPledge%TYPE;
Pay_amt dd_payment2.Payamt%TYPE;
Pay_date dd_payment2.Paydate%TYPE;
Pay_method dd_payment2.Paymethod%TYPE;
BEGIN
id_pay := :NEW.idpay;
id_Pledge := :NEW.IDPLEDGE;
Pay_amt := :NEW.PAYAMT;
Pay_date := :NEW.PAYDATE;
Pay_method := :NEW.PAYMETHOD;
IF INSERTING THEN
log_action := 'Insert';
INSERT INTO dd_payment2(idpay, idpledge, payamt, paydate, paymethod,pt_action)
VALUES (id_pay, id_Pledge, Pay_amt, TO_CHAR(Pay_date, 'DD-MON-YY'), Pay_method,log_action);
ELSIF UPDATING THEN
log_action := 'Update';
UPDATE DD_PAYMENT2 SET PAYAMT=Pay_amt,PAYDATE=TO_CHAR(Pay_date, 'DD-MON-YY'),pt_action='UPDATE' WHERE idpay=id_pay;
ELSIF DELETING THEN
id_pay := :OLD.idpay;
log_action := 'Delete';
UPDATE DD_PAYMENT2 SET pt_action='DELETE' WHERE idpay=id_pay;
ELSE
DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;
END pledge_pay_trigger2;
/
COMMIT;
// if log_action ='Insert' then
INSERT INTO dd_payment2(idpay, idpledge, payamt, paydate, paymethod,pt_action)
VALUES (id_pay, id_Pledge, Pay_amt, TO_CHAR(Pay_date, 'DD-MON-YY'), Pay_method,log_action);
else if log_action ='Update' then
UPDATE DD_PAYMENT2 SET PAYAMT=Pay_amt,PAYDATE=TO_CHAR(Pay_date, 'DD-MON-YY'),pt_action='UPDATE' WHERE idpay=id_pay;
else
UPDATE DD_PAYMENT2 SET pt_action='DELETE' WHERE idpay=id_pay;
end if
ALTER TABLE DD_PAYMENT2 ADD pt_action VARCHAR2(30 BYTE);
COMMIT;
INSERT INTO dd_payment(idpay, idpledge, payamt, paydate, paymethod)
VALUES (1470, 105, 250, SYSDATE, 'CC');
commit;
UPDATE dd_payment
SET payamt = 2000
WHERE idpay = 1470;
COMMIT;
DELETE
FROM dd_payment
WHERE idpay = 1470;
COMMIT;
