Trigger for insert 1

[mmayo]

I have composed this trigger:

CREATE OR REPLACE TRIGGER JobCost
after update [clock_out]
ON CIM_TIMECLOCK

BEGIN

INSERT INTO FORJOBCOST
SELECT CIM_FOLDER11.JOB_NUMBER, CIM_FOLDER11.OP_NO,
CIM_FOLDER11.MACHINE, CIM_FOLDER11.QTY_GOOD,
CIM_FOLDER11.QTY_SCRAP, CIM_TIMECLOCK.OPERATOR,
CIM_TIMECLOCK.CLOCK_IN, CIM_TIMECLOCK.CLOCK_OUT,
CIM_FOLDER07.DAT, CIM_FOLDER07.TIM,
CIM_FOLDER07.ACT_CODE, CIM_FOLDER08.VALU
FROM CIM_FOLDER07, CIM_FOLDER11, CIM_TIMECLOCK,
CIM_FOLDER08
WHERE CIM_FOLDER07.SESSION_ID = CIM_FOLDER11.SESSION_ID
AND CIM_FOLDER07.SESSION_ID = CIM_TIMECLOCK.SESSION_ID
AND CIM_FOLDER07.ACT_CODE = CIM_FOLDER08.MEAN_CODE

END;

If I execute the body as a stored procedure it works correctly. But when I try to create it as a trigger (using VB and data project), I get the message "ADO error: ORA-00969: Missing ON keyword".

Any comment or direction would be appreciated!

 

[sem]

Get rid of square brackets around clock_out:

CREATE OR REPLACE TRIGGER JobCost
after update of clock_out
ON CIM_TIMECLOCK
...

 

[mmayo]

sem

Thanks! You probably guessed this is my first attempt at a trigger. Your answer was absolutely correct.

Can I trouble you with an additional question? (If the answer is 'no' please stop reading now!) The record that fires the trigger contains a field called session_id. I would like to pass that to the insert statement. What would be the correct way to declare variable?

Thank-you in advance.

 

[sem]

The trigger you create is not fired by an appropriate record, it's so-called STATEMENT level trigger. To fire it for each processed record you should use FOR EACH ROW clause. Then you may reference field value as :new.session_id and ld.session_id (you may change prefixes using REFERENCES clause).

So my advice for you is to read CREATE TRIGGER statement syntax carefully as well as articles/threads about mutating trigger problem (you'll hit it further).

 

[mmayo]

Sem -
Thanks for your time and help! You deserve a star!

 

[mmayo]

As a follow up for any who may be having a similar problem: there is alot to read regarding 'mutating tables'! Please take the time to research! And, as sem indicated, it needs to be a row level trigger.

Here is the solution I'm using (I still have PLENTY of coding to do, so this may still change. But for the moment...):

CREATE OR REPLACE TRIGGER CIM_TIMECLOCK_JOBCOST
AFTER UPDATE of CLOCK_OUT ON CIM_TIMECLOCK
For each row
BEGIN

INSERT INTO FORJOBCOST (JOB_NUMBER, OP_NO, MACHINE, QTY_GOOD,
QTY_SCRAP, OPERATOR, CLOCK_IN, CLOCK_OUT, DAT, TIM, ACT_CODE, VALU)
SELECT CIM_FOLDER11.JOB_NUMBER, CIM_FOLDER11.OP_NO,
CIM_FOLDER11.MACHINE, CIM_FOLDER11.QTY_GOOD,
CIM_FOLDER11.QTY_SCRAP,
:new.OPERATOR,
:new.CLOCK_IN,
:new.CLOCK_OUT,
CIM_FOLDER07.DAT, CIM_FOLDER07.TIM,
CIM_FOLDER07.ACT_CODE, CIM_FOLDER08.VALU
FROM CIM_FOLDER07, CIM_FOLDER11, CIM_FOLDER08
WHERE CIM_FOLDER07.SESSION_ID = CIM_FOLDER11.SESSION_ID
AND CIM_FOLDER07.SESSION_ID = ld.session_ID
AND CIM_FOLDER07.ACT_CODE = CIM_FOLDER08.MEAN_CODE
AND CIM_FOLDER11.SESSION_ID = ld.session_ID;

END;