title: PL/SQL trigger for after insert, update, delete that enters Data into Log Table with a Sequence
body: <p>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. <a href="http://www.dbforums.com/informix/1628545-create-sequence-create-trigger.html" rel="nofollow">I have read that using SERIAL not null on the Table column entry may eliminates the need for a sequence and referencing table</a>, but I need that for my homework to work.</p>
  
  <p>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.  <a href="http://www.razorsql.com/features/oracle_add_sequence_trigger.html" rel="nofollow">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.</a></p>
  
  <p>And of course the <a href="http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/triggers.htm#" rel="nofollow">Oracle 11g Reference Doc for Triggers, I've gone over it but you can check it out yourself too.</a></p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <pre><code>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                   
  </code></pre>
  
  <p>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.  </p>
  
  <p>*<strong><em>I fixed it, I had to add my own Update, Insert, and Delete statements after compiling the Trigger.  Works great now.  Thanks Noel!!!</em>*</strong></p>
  
  <pre><code>    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; 
  </code></pre>
  
  <p>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.   </p>
  
  <pre><code>-- 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,'mtrea492@mdv.com','Y','01-SEP-2012');
  INSERT INTO dd_donor  
    VALUES (302, 'Patrick', 'Lee', 'I','11 Hooper St.', 'Norfolk',     
  'VA','23510','7572115445',NULL,'pleeNorf@gmail.com','N','09-SEP-2012');
  INSERT INTO dd_donor  
    VALUES (303, 'Terry', 'Venor', 'I','556 Loop Lane.', 'Chesapeake', 
  'VA','23320',NULL,NULL,'tervenr@drw.edu','Y','18-SEP-2012');
  INSERT INTO dd_donor  
    VALUES (304, 'Sherry', 'Pane', 'I','Center Blvd.', 'Virginia Beach', 
  'VA','23455',NULL,NULL,'toppane@yahoo.com','Y','21-SEP-2012');
  INSERT INTO dd_donor  
    VALUES (305, 'Thomas', 'Sheer', 'I','66 Train St.', 'Chesapeake', 
  'VA','23322','7579390022',NULL,'tls3488@sheer.com','Y','01-MAR-2013');
  INSERT INTO dd_donor  
    VALUES (306, NULL, 'Coastal Developers', 'B','3667 Shore Dr.', 'Virginia Beach', 
  'VA','23450','8889220004',NULL,'coastVA@cdev.com','Y','30-SEP-2012');
  INSERT INTO dd_donor  
    VALUES (307, NULL, 'VA Community Org', 'G','689 Bush Dr.', 'Norfolk', 
  'VA','23513','7578337467','7578337468','vacmorg@biz.com','Y','03-OCT-2012');
  INSERT INTO dd_donor  
    VALUES (308, 'Betty', 'Konklin', 'I','11 Shark Ln.', 'Virginia Beach', 
  'VA','23455','7574550087',NULL,'shark11@cox.net','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,'UDezigns@cox.net','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;
  </code></pre>