This discussion is archived
14 Replies Latest reply: May 3, 2012 6:34 AM by 934737 RSS

If statements within a trigger

934737 Newbie
Currently Being Moderated
Hello there,

I've been trying to create a trigger in which when a job is completed, an invoice sheet will be generated, using the jobs' entities (date started, completed, cost etc.) to fill in the fields of the invoice.

First of all, this is what I've started with:
CREATE OR REPLACE TRIGGER gen_invoice
AFTER UPDATE ON jobs FOR EACH ROW
BEGIN
IF :new.status = 'Completed' THEN
DECLARE @Amount NUMBER(8, 2)
SET @Amount = SELECT SUM(unitCost * qtyUsed + rate + hrs) FROM jobAssign
WHERE jobNumber = jobs.jobNumber;
INSERT INTO invoice values (invoice_sequence.nextval, jobs.proposalNo, SYSDATE, SYSDATE + 10,
jobs.dateGen, jobs.dateReq, jobs.location, Amount, SYSDATE, '10', '100', '10', 'Not Paid');
END IF;
END;
/
Basically, I wanted to declare a variable known as 'Amount' and use some fields from jobs to calculate a total amount. I get the following error though:
 ERROR at line 9: PLS-00103: Encountered the symbol "IF" when expecting one of the following: ; delete exists prior 
The above is what I truly need. However, before I get the above fixed, I thought I might create another trigger which did not declare any variables or did not consist of the if statements, just so I can test if the data was inserted properly. So I used:
CREATE OR REPLACE TRIGGER gen_invoice
AFTER UPDATE ON jobs FOR EACH ROW
BEGIN
INSERT INTO invoice VALUES (invoice_sequence.nextval, jobs.proposalNo, SYSDATE, SYSDATE + 10,
jobs.dateGen, jobs.dateReq, jobs.location, Amount, SYSDATE, '10', '100', '10', 'Not Paid');
SELECT proposalNo
FROM proposals
WHERE proposalNo = jobs.proposalNo
AND jobs.status = 'Completed';
END;
/
However, this also did not work. The error come up with:
 ERROR at line 2: PL/SQL: ORA-00917: missing comma 
My main goal is try to get the first set of statements to work. So any help is appreciated!

Thanks.

P.S. I'm using oracle version 10.2.0.1.0 if that helps.
  • 1. Re: If statements within a trigger
    908002 Expert
    Currently Being Moderated
    What ever u wrote is not oracle syntax, its sqlservers T-SQL.

    updated as per oracle syntax.

    and changed job.column name to :new.column name as youw rote a trigger on jobs table
    CREATE OR REPLACE TRIGGER gen_invoice
    AFTER UPDATE ON jobs FOR EACH ROW
    declare
    Amount NUMBER(8, 2)
    BEGIN
    IF :new.status = 'Completed' THEN
     SELECT SUM(unitCost * qtyUsed + rate + hrs) into amount FROM jobAssign
    WHERE jobNumber = :new.jobNumber;
    
    INSERT INTO invoice values (invoice_sequence.nextval, :new.proposalNo, SYSDATE, SYSDATE + 10,
    :new.dateGen, :new.dateReq, :new.location, Amount, SYSDATE, '10', '100', '10', 'Not Paid');
    END IF;
    END;
    / 
  • 2. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thanks for that Kiran.

    However, I'm still getting errors:
    ERROR at line 7: PLS-00049: bad bind variable ':new.proposalNo'
    So I tried removing "new" within the insert statements and then I got:
    ERROR at line 3: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: := ; not null default character The symbol ";" was substituted for "BEGIN" to continue.
    I should note there is 3 tables I'm trying to work with: invoice, jobs, proposal. Theres a relationship between invoice and proposal, and a relationship between jobs and proposal. Hence, when status within jobs is updated to 'Completed', invoice will retrieve all of jobs information from proposalNo, if that makes sense.

    Thanks
  • 3. Re: If statements within a trigger
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user13150012 wrote:
    Thanks for that Kiran.

    However, I'm still getting errors:
    ERROR at line 7: PLS-00049: bad bind variable ':new.proposalNo'
    So I tried removing "new" within the insert statements and then I got:
    ERROR at line 3: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: := ; not null default character The symbol ";" was substituted for "BEGIN" to continue.
    Kiran forgot the required semicokon at the end of
    Amount NUMBER(8, 2)
    That's quite understandable, since you haven't posted enought for anyone to test the trigger.
    There are other errors too, but how to fix them depends on your tables, which I don't have, so I can't say how to fix those errors.
    I should note there is 3 tables I'm trying to work with: invoice, jobs, proposal. Theres a relationship between invoice and proposal, and a relationship between jobs and proposal. Hence, when status within jobs is updated to 'Completed', invoice will retrieve all of jobs information from proposalNo, if that makes sense.

    Thanks
    Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    In the case of a DML operation (such as this problem) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed tables after the DML. Post a statement or two that sholuld fire the trigger, and show what the tables should contain after each one.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using.
  • 4. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thanks for the reply Frank.

    First of all, I've tried adding the semicolon after the statement you mentioned and still had an error.
    CREATE OR REPLACE TRIGGER gen_invoice
    AFTER UPDATE ON jobs FOR EACH ROW
    declare
    Amount NUMBER(8, 2);
    BEGIN
    IF :new.status = 'Completed' THEN
     SELECT SUM(unitCost * qtyUsed + rate + hrs) into Amount FROM jobAssign
    WHERE jobNumber = jobs.jobNumber;
     
    INSERT INTO invoice values (invoice_sequence.nextval, proposalNo, SYSDATE, SYSDATE + 10,
    dateGen, dateReq, location, Amount, SYSDATE, '10', '100', '10', 'Not Paid');
    END IF;
    END;
    / 
     ERROR at line 7: PL/SQL: SQL Statement ignored 
    I will further explain my problem with sample tables/data:

    Tables:

    Table proposal = (proposalNo, customerNo, other_fields_not_related)
    Table jobs = (jobNumber, proposalNo, dateGen, dateReq, location, status, other_fields_not_related)
    Table jobsAssign = (assignNo, jobNumber, unitCost, qtyUsed, rate, hrs, other_fields_not_related)
    Table invoice = (invoiceNo, proposalNo, dateGen, dDate, sDate, eDate, task, location, amount, cDate, totalBeforeTax, tax, totalWithTax, Status)

    Example CREATE TABLE:
    create table proposal  (
       proposalNo       INTEGER                         not null,
       customerNo       INTEGER                         not null,
       other_fields_not_related,
       constraint PK_proposal primary key (proposalNo)
    );
    
    create table jobs  (
       jobNumber       INTEGER                         not null,
       proposalNo         INTEGER                         not null,
       dateGen               DATE,
       location   VARCHAR2(100),
       status               VARCHAR2(20),
       dateReq        DATE,
       other_fields_not_related,
       constraint PK_jobs primary key (jobNumber)
    );
    
    create table jobsAssign  (
       assignNo       INTEGER                         not null,
       jobNumber       INTEGER                         not null,
       unitCost            NUMBER(8,2),
       qtyUsed             NUMBER,
       rate                 NUMBER(8,2),
       hrs           NUMBER,
       other_fields_not_related,
       constraint PK_jobsAssign primary key (assignNo)
    );
    
    create table invoice  (
       invoiceNo          INTEGER                         not null,
       proposalNo         INTEGER                         not null,
       dateGen               DATE,
       dDate             DATE,
       sDate           DATE,
       eDate             DATE,
       task                 VARCHAR2(200),
       location             VARCHAR2(100),
       amount               NUMBER(8,2),
       cDate        DATE,
       totalBeforeTax     NUMBER(8,2),
       tax                  NUMBER(8,2),
       totalWithTax      NUMBER(8,2),
       status               VARCHAR2(20),
       constraint PK_invoice primary key (invoiceNo)
    );
    example INSERT DATA:
    insert into proposal
         select '100', '10000' from dual /* other non related fields not shown */
    union all
         select '101', '10001',  from dual
    union all
         select '102', '10002',  from dual;
    
    insert into jobs
         select '0001', '100', '23-OCTOBER-2010', '321 Work Street, Hunters Hill',
         'Completed', '25-OCTOBER-2010', from dual /* other non related fields not shown */
    union all
         select '0002', '101', '24-APRIL-2011', '321 Worker Street, Sunny Hill',
         'Completed', '30-APRIL-2011' from dual
    union all
         select '0003', '102', '11-JUNE-2011', '321 Working Street, Rainy Hill',
         'Pending', '13-JUNE-2011', 'Adam Jenson', '15-JUNE-2011' from dual;
    
    insert into jobsAssign
         select '00001', '0001', '4.00', '8', '12.50', '4' from dual /* other non related fields not shown */
    union all
         select '00002', '0002', '6.30', '3', '13.30', '3' from dual
    union all
         select '00003', '0003', '2.50', '8', '15.30', '3' from dual;
    With the above insert data, when the pending of a job is changed to complete, it should fire the trigger. Also, perhaps create invoices for the already completed if the invoices are not yet generated.

    The relationship between these tables:

    * invoice - proposal: many-to-one
    * proposal - jobs: one-to-many
    * jobs - jobsAssign: one-to-many

    So basically, I want the trigger to fire when the status within the jobs table is changed to 'Completed', it will generate an invoice containing data from jobsAssign table (to calculate total amount etc.) and the related proposalNo from the proposal table. NOTE: I'm using sqldeveloper, so I'm using the interface to update data.

    I hope that cleared it up a bit more.

    Thanks

    Edit 1: Included example create table.
    Edit 2: Added more CREATE table statements and also include INSERT statements

    Edited by: user13150012 on May 3, 2012 3:45 AM

    Edited by: user13150012 on May 3, 2012 4:03 AM
  • 5. Re: If statements within a trigger
    Paul Horth Expert
    Currently Being Moderated
    I hope that cleared it up a bit more.

    Not really. You didn't supply the create table and sample insert statements requested.
    You didn't supply your current trigger and the new error message you are getting.
  • 6. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thanks for the reply,

    I've added the create table example in the previous post.
  • 7. Re: If statements within a trigger
    Paul Horth Expert
    Currently Being Moderated
    Yes, for one of the four tables. Where are the others?

    And still no test data INSERTS :-(
  • 8. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thanks for the reply again.

    I added (hopefully) enough of what you have requested :)!
  • 9. Re: If statements within a trigger
    kendenny Expert
    Currently Being Moderated
    CREATE OR REPLACE TRIGGER gen_invoice
    AFTER UPDATE ON jobs FOR EACH ROW
    declare
      Amount NUMBER(8, 2);
    BEGIN
      IF :new.status = 'Completed' and :old.status != 'Completed' THEN
        SELECT SUM(unitCost * qtyUsed + rate + hrs) into Amount FROM jobAssign
         WHERE jobNumber = jobs.jobNumber;
    
        INSERT INTO invoice values (invoice_sequence.nextval, :new.proposalNo, SYSDATE, SYSDATE + 10,
          :new.dateGen, :new.dateReq, :new.location, Amount, SYSDATE, '10', '100', '10', 'Not Paid');
      END IF;
    END;
    /
    That should do it. In case something else changes after the status is set to Completed, you don't want to do this again so I added and :old.status != 'Completed' to the condition. All columns from the record being updated need to have ":new." qualification (or :old. if you want the previous value).
  • 10. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thanks kendenny,

    however I still get an error:
     ERROR at line 8: PLS-00049: bad bind variable 'NEW.proposalNo' 
    I think that is because the statement doesn't know which table to get those values from (proposalNo, dateGen, dateReq, location etc.) from jobs. I've tried doing a select clause within the if statement before (read first post) but I couldn't get it to work.
  • 11. Re: If statements within a trigger
    Paul Horth Expert
    Currently Being Moderated
    Try
    create or replace trigger gen_invoice
      after update on jobs
      for each row
    declare
      amount number(8
                   ,2);
    begin
      if :new.status = 'Completed'
         and :old.status != 'Completed' then
        select sum(unitcost * qtyused + rate + hrs)
          into amount
          from jobsassign
         where jobnumber = :new.jobnumber;
      
        insert into invoice
          (invoiceno
          ,proposalno
          ,dategen
          ,ddate
          ,sdate
          ,edate
          ,location
          ,amount
          ,cdate
          ,totalbeforetax
          ,tax
          ,totalwithtax
          ,status)
        values
          (invoice_sequence.nextval
          ,:new.proposalno
          ,sysdate
          ,sysdate + 10
          ,:new.dategen
          ,:new.datereq
          ,:new.location
          ,amount
          ,sysdate
          ,'10'
          ,'100'
          ,'10'
          ,'Not Paid');
      end if;
    end;
    /
  • 12. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thanks Paul, however I still get an error :(
     ERROR at line 8: PL/SQL: SQL Statement ignored 
    I looked up what this error meant and it seems that the line:
     where jobnumber = :new.jobnumber; 
    is not defined.

    I think it has to be
     where jobnumber = jobs.jobnumber; 
    otherwise, how would select which job has been completed (the relationship between jobs and jobsAssign)

    Also, there is still that problem of from the previous post where
     :new.proposalNo, :new.dateGen, :new.dateReq, :new.location 
    are not found because im not selecting them from the jobs table which I can't seem to figure out.
  • 13. Re: If statements within a trigger
    Paul Horth Expert
    Currently Being Moderated
    Strange, it worked for me when I created the trigger, I even got an invoice out of it! :-)

    You need the :new.jobnumber - it knows which one because that's the row that caused the trigger to fire.

    Are you sure you are copying this exactly?

    Post EXACTLY what you have now.

    Edited by: Paul Horth on 03-May-2012 06:10
  • 14. Re: If statements within a trigger
    934737 Newbie
    Currently Being Moderated
    Thank you so much Paul!

    I missed an expression (stupid me :( )

    Nevertheless, thank you very much !

    P.S. Thanks for explaining the :new. keyword in this example to me!

    Edited by: user13150012 on May 3, 2012 6:34 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points