Very clean Tom, thanks. Though meager is my wisdom when compared to that of Tom, even a blind squirl finds an acorn now and then, so please allow me to offer my explanation of merge vs. "update else insert" (aka. upsert). It seems to me that the confusion around merge may arise from thinking about merge as an equivelant for the "update else insert" (aka. upsert) sequence of logic we are all familiar with. Indeed this is discussed at length all over the internet.

In short I am confident that merge behaves just like Oracle wants it to. But it is a mistake to assume that merge is a simple replacement for upsert. Consider this sequence of code:

update t1 set c1 = 1 where 1 = 0;
if sql%rowcount = 0 then insert into t1 values ... end if;

This is clearly an old style upsert sequence. Since the update can't ever update anything, the insert will fire and create a new row. So, given the following triggers, what triggers should fire?

bu, bur, au, aur, bi, bir, ai, air

For our upsert sequence, we expect the following triggers to fire in this sequence:

bu, au, bi, bir, air, ai

The example of merge provided by Dushan (a fine example), clearly shows this to be the case. The merge fired the same triggers that the upsert logic sequence fired. If the merge only executed the insert triggers because in the end it only did an insert, then it wouldn't be duplicating the upsert concept.

This may be the first hurdle to jump in understanding merge. That for each row it is considering, merge is effectively doing two dml operations, yet only one of them will actually affect a row. But it does this in the context of a single dml statement.

However, the example also shows that merge is not an exact replacement for upsert. We can see that the sequence in which the triggers fired was different. I presume this is because merge being one statment (as vs. two with upsert), follows the basic rules of trigger execution order.

Ah but it gets still more interesting as we note that "update else insert" can also be written as some variation of "insert else update". It does the same thing right? Well, maybe. For if we had written our upsert using an "insert else update" variation, then the triggers being executed would have been: bi, bir, air, ai; no mention of the update at all. And here in may be the original confusion for this is clearly not the same as the merge we saw. "Insert else update" had two triggers that didn't fire when compared to "update else insert" and merge. The reverse would also be true if we had written an update statement that worked, because our upsert sequence would not have done any insert and hence not have fired any insert triggers. So merge and upsert are not the same.

While we are at it, is there any significance to why merge fired the bi trigger first rather than the bu trigger. Maybe its just one of those interesting yet usless details. I suppose one has come first so why not insert.

In the end, upsert is loose. It is a concept that as developers we define when we code it. Merge can't afford to be loose, it is a real statment all on its own. Merge has to decide on a behavior and stick to it, which it does nicely. If we use merge, we simply need to remember it is similar to but not identical to upsert; as is demonstrated by our examples of merge, the two varations of upsert, and the differences in triggers being fired in each scenario.

Thanks, Kevin from Norwich CT

Well, what to say? Simply to remember:
"Do not use merge if you have statement level triggers on table" (Actually, this stopped us from implementing merge)
OR
"Do not write statement level triggers if you use merge statement for table"
Anyway, it is a bit dissapointing until we get used that merge statement behaves this way.

Thanks,

Dushan

The difficulty is fundamental ambiguity between

update emp
set sal = 1000
where ename = 'KING'

and

delete from emp
where ename = 'KING'
insert into emp
values ('KING',...,1000);

They are logically the same, but triggering actions are different. This is one of the reasons why triggers fare pretty low from database theory perspective. The more "nerge", "upset" and the other nonsence is invented -- the more confusing database practice becomes.

Thank you Mikito, an excellent summary. You hit the nail right on the head. The ambiguity question is nothing new, just exposed in a new light because of a new feature.

I am not placing any kind of value judgement on merge. I am not suggesting that anyone should use merge, nor that anyone should not use merge. This is a decision that each of us must make for ourselves.

Merge is another tool available to us. Like any tool, we should know how to use if safely and use it when the tool is appropriate for what we want to do.

Good discussions, thanks, Kevin

"Silly" is if you have such a statement level triggers from third party (ORACLE Designer, TAPI (ok, you have said your opinion about TAPI)) and you cannot use MERGE.
Maybe this should be addessed to ORACLE Designer developers team (TAR?) ...

<quote>They are logically the same ...</quote>

Well, if ename were a candidate key then you _could_ argue the sameness. As a generic statement though, it is clearly not true.

Cheers.

OK, just cause I like a good discussion let me take the "not oracle" side and argue it (since oracle has Tom and thus doesn't need me). First let me reiterate, I think merge is doing what oracle wants it to do. That said, here is what I see as the issue that some people have with merge; (I will psuedo code stuff here so don't anybody get upset with me):

update t1 ...;
if sql%rowcount = 0 then insert into t1 ...; end if;

This is the typical upsert. Update the row if it exists otherwise insert it. But one could argue this is sloppy coding and that the upsert should be coded as this:

if "row exists in t1" then
update t1 ...
else
insert into t1 ...
end if;

Why could one argue that the first is sloppy?, well, basically because it is. It does work that is not necessary (one of the dml operations it is attempting will not do anything every time so why was it done?). This in turn will cause triggers to fire that don't need to be firing. This may or may not be an issue depending upon what is in the triggers.

The second version of the upsert doesn't do any of this. It updates or it inserts as needed based on existence of data but not both. It is in short more direct than the first version of code.

Ideally, in the end the data should look the same, but the path to getting there will be different between the two and that means opportunity for differences in results.

I am sure somebody here can come up with plenty of examples where executing these "extra" "unexpected" triggers can cause problems with otherwise healthy trigger code. Auditing triggers might be a good place to start.

I am not advocating use nor non-use of merge. I am not say that there is or is not anything wrong with merge. I am pointing out this fact:

When people learn and read about merge, they get the sense of "conditional upsert". But when they use it they see "sloppy upsert", and they don't like it.

OK who has comments?

Kevin

Usually, we take it for granted that if one writes

x = 100* (a + b)/2;
y = 10* (a + b)/2;

then compiler would optimize this code snippet to calculate common subexpression once only.

Tom insists that searching for the same row twice within the same transaction is suboptimal. Then, likewise, why RDBMS doesn't optimize it?

BTW, the first style is like conditional statement in traditional programming, while the other one is like exception handling: "update; if exception, then insert". Both styles have their strengths and weaknesses.

I don't understand this part of your answer.

Certainly, two queries within a transaction see the same state of the database, provided that this transaction didn't touch this state in the DML between the queries.

BTW, it is possible to cache intermediate query results even if no longer assume that we are in a scope of the same transaction...

certainly?  you have been using the database right?


you believe

select * from t where x = 5;
update t set y = 6 where x = 5;

will necessary 'see' the same rows that is updates???????
ops$tkyte@ORA9IR2> select * from t where x = 5;
 
         X          Y
---------- ----------
         5          5
 
ops$tkyte@ORA9IR2> pause
 
ops$tkyte@ORA9IR2> update t set y = 6 where x = 5;
 
0 rows updated.
 
ops$tkyte@ORA9IR2>

<b>that is a direct cut and paste, no edits.   if you would like, we could make the update see billions of records -- or just one record (and a different "x=5" record at that</b>





you need to read the concepts guide and get back to basics.  Unless all you work on are single user systems anyway.  If you are relying on something like that in your code -- you need to have it reviewed, it is wrong.


And I fail to see the relevance of the last paragraph in the context of this discussion. 

I have created TAPI code (package + triggers) from ORACLE Designer for the table DT (DT_JN is journalling table).
I do not want to add gerenated scripts, it's lots of rows. If there is a way, I would send you all the scripts.

Here we go!

valasekd@SCM.DUMMY.CZ> insert into dt(code,dt_desc) values('A','desc of A')
2 /

1 row created.

Elapsed: 00:00:00.00
valasekd@SCM.DUMMY.CZ> select * from dt
2 /

ID COD DT_DESC
--------- --- ------------------------------
1 A desc of A

Elapsed: 00:00:00.00
valasekd@SCM.DUMMY.CZ> update dt set dt_desc='desc of A1' where code='A'
2 /

1 row updated.

Elapsed: 00:00:00.00
valasekd@SCM.DUMMY.CZ>
valasekd@SCM.DUMMY.CZ> select * from dt
2 /

ID COD DT_DESC
--------- --- ------------------------------
1 A desc of A1

Elapsed: 00:00:00.00
valasekd@SCM.DUMMY.CZ>
valasekd@SCM.DUMMY.CZ> select * from dt_jn
2 /

JN_ JN_ORACLE_USER JN_DATETI
--- ------------------------------ ---------
JN_NOTES
--------------------------------------------------------------------------------
--------------------
JN_APPLN JN_SESSION ID COD DT_DESC
----------------------------------- ---------- --------- --- -------------------
-----------
INS VALASEKD 21-OCT-04

CG$DT.INS 4096 1 A desc of A

UPD VALASEKD 21-OCT-04

CG$DT.UPD 4096 1 A desc of A1



valasekd@SCM.DUMMY.CZ> -- This is OK. Now lets try merge
valasekd@SCM.DUMMY.CZ> merge into dt a
2 using (select 'A1' code
3 ,'desc of A1 merge' dt_desc
4 from dual) b
5 on (a.code=b.code)
6 when matched then
7 update set a.dt_desc=b.dt_desc
8 when not matched then
9 insert(code,dt_desc) values (b.code,b.dt_desc)
10 /
merge into dt a
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "VALASEKD.CG$AUS_DT", line 17
ORA-04088: error during execution of trigger 'VALASEKD.CG$AUS_DT'


Elapsed: 00:00:00.00
valasekd@SCM.DUMMY.CZ>
valasekd@SCM.DUMMY.CZ>
valasekd@SCM.DUMMY.CZ> merge into dt a
2 using (select 'A2' code
3 ,'desc of A2 merge' dt_desc
4 from dual
5 ) b
6 on (a.code=b.code)
7 when matched then
8 update set a.dt_desc=b.dt_desc
9 when not matched then
10 insert(code,dt_desc) values (b.code,b.dt_desc)
11 /
merge into dt a
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "VALASEKD.CG$AUS_DT", line 17
ORA-04088: error during execution of trigger 'VALASEKD.CG$AUS_DT'


Elapsed: 00:00:00.00
valasekd@SCM.DUMMY.CZ>

-- here is the trigger cg$AUS_DT

valasekd@SCM.DUMMY.CZ> CREATE OR REPLACE TRIGGER cg$AUS_DT
2 AFTER UPDATE ON DT
3 DECLARE
4 idx BINARY_INTEGER := cg$DT.cg$table.FIRST;
5 cg$old_rec cg$DT.cg$row_type;
6 cg$rec cg$DT.cg$row_type;
7 cg$ind cg$DT.cg$ind_type;
8 BEGIN
9 -- Application_logic Pre-After-Update-statement <<Start>>
10 -- Application_logic Pre-After-Update-statement << End >>
11
12 WHILE idx IS NOT NULL LOOP
13 cg$old_rec.ID := cg$DT.cg$table(idx).ID;
14 cg$old_rec.CODE := cg$DT.cg$table(idx).CODE;
15 cg$old_rec.DT_DESC := cg$DT.cg$table(idx).DT_DESC;
16
17 IF NOT (cg$DT.called_from_package) THEN
18 idx := cg$DT.cg$table.NEXT(idx);
19 cg$rec.ID := cg$DT.cg$table(idx).ID;
20 cg$ind.ID := updating('ID');
21 cg$rec.CODE := cg$DT.cg$table(idx).CODE;
22 cg$ind.CODE := updating('CODE');
23 cg$rec.DT_DESC := cg$DT.cg$table(idx).DT_DESC;
24 cg$ind.DT_DESC := updating('DT_DESC');
25
26 cg$DT.validate_foreign_keys_upd(cg$rec, cg$old_rec, cg$ind);
27
28 cg$DT.upd_denorm2( cg$rec,
29 cg$DT.cg$tableind(idx)
30 );
31 cg$DT.upd_oper_denorm2( cg$rec,
32 cg$old_rec,
33 cg$DT.cg$tableind(idx)
34
);
35 cg$DT.cascade_update(cg$rec, cg$old_rec);
36 cg$DT.domain_cascade_update(cg$rec, cg$ind, cg$old_rec);

37
38 cg$DT.called_from_package := FALSE;
39 END IF;
40 idx := cg$DT.cg$table.NEXT(idx);
41 END LOOP;
42
43 cg$DT.cg$table.DELETE;
44
45 -- Application_logic Post-After-Update-statement <<Start>>
46 -- Application_logic Post-After-Update-statement << End >>
47
48 END;
49 /

Trigger created.

The "Reader" above was Dushan.

Sorry for that!

Dushan

Dushan, surely there is a bug in your trigger code? :-

18 idx := cg$DT.cg$table.NEXT(idx);
19 cg$rec.ID := cg$DT.cg$table(idx).ID;

You have incremented idx to the next value, but what if you had already been on the last value? idx would then be NULL.

If this trigger comes from a generated TAPI, then surely that has a bug, not the MERGE statement!

Kevin said: 'I am sure somebody here can come up with plenty of examples where executing these "extra" "unexpected" triggers can cause problems with otherwise healthy trigger code. Auditing triggers might be a good place to start.'

I guess it shows that you must be careful with statement-level triggers: don't assume that the firing of a statement-level trigger means that anything actually "happened". It means that the user initiated a statement that had the potential to do something. If your statement-level triggers have problems with such "null" updates, then they are NOT healthy triggers after all!

<quote>
the ugly code is the

if row exists
then
update
else
insert
end if -- that is mightly ugly
</quote>

Clearly, "if row exist" is "select for update". Your counterexample doesn't work in this case.

if row exists
then
update
else
insert
end if -- that is mightly ugly


update
if (sql%rowcount=0)
then
insert
end if


Yes, the first version of the code does consume more resources than the second. But thats another topic. This discussion here was centered around the behavior of merge. Performance is important and in the days before triggers we all wrote code like version two (assuming we took the time to test the alternatives to see that is was better) because it used less resources to do the same thing. But today's oracle allows for triggers, and in the presence of triggers these two pieces of code that used to be equivalent are no longer. They are no longer equivalent because the path to get to the end result in no longer the same. Ideally the path to your destination shouldn't matter, but it does.

Yes, the end result on the primary data being manipulated should be the same for both versions (proper update/insert will be done). We are concerned about what else is happening beyond the primary update/insert.

No,... an update that updates zero rows may in fact do something. In this case it fires a statement level trigger and that trigger does something otherwise it should not be there.

As I pointed out earlier, this is not new news. This "side affect" (if I can call it that though somebody is bound to jump on use of the phrase), is inherent in the use of statement level triggers. Merge simply causes the issue to become more apparent to some people because detailed inspection of its operation as Dushan did, shows a behavior for merge that they didn't expect.

To Tony's point about a healthy trigger, yep, if you are going to use statement level triggers, you need to write the code to make sure it doesn't do something dumb in the presence of dml that updates or inserts zero rows. Its just the price to pay if you want to use statement level triggers.

I use triggers a lot, but not statement level triggers. Mostly because I prefer putting all logic in an instead of trigger of a view layer. But, even this is not for everyone I suppose.

I am afraid I have to bow out of further discussion on this topic, a pile of work just showed up in my INBOX. I believe we have all clearly stated our points well enough of others to come to their own conclusions. Again, I don't think anything is wrong, its just the way stuff works.

Don't worry Dushan, you are in excellent hands in this forum. You will find out or figure out what you need to know. If our jobs were easy we would all be Monkeys working for peanuts. Uhoh... I feel my ringed tail twitching... must be the boss coming over to crank the grind on the organ again; they call me "Mateo".

Thanks guys, lots of fun today, Kevin

Tom-- I took the example from your initial response, and added another trigger with a C(R)UD code.  The response was interesting with a MERGE... neither inserting nor updating!

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger tuidbs_t before
  2  insert or update or delete on t
  3  declare
  4    x number;
  5    cud_cd char(1) :=
  6                      case when inserting then 'I'
  7                           when deleting then 'D'
  8                           when updating then 'U'
  9                           else '?' end;
 10  BEGIN
 11    DBMS_OUTPUT.PUT_LINE('cud = '||cud_cd);
 12* end;
SQL> /

Trigger created.

SQL> merge into t
   2   using (select * from dual where 1=0)
   3   on (t.x = dummy)
   4   when matched then update set y = 5
   5   when not matched then insert values (1,1);
cud = ?
before insert
cud = ?
before update
after update
after insert

0 rows merged.

SQL> insert into t select 1,1 from dual;
cud = I
before insert
after insert
 

I think the key takeaway here is that MERGE introduces a new potential pattern of trigger calls that may introduce bugs in code that used to work.

For example, lets say that you have statement level pre/post insert and update triggers.

The pre- triggers do setup code and the post triggers do cleanup code.

Before Merge you could be guaranteed that if the statement succeeded then you never had to pre- triggers fire in a row - it would always be Pre then Post.

Now you will have Pre Insert and Pre Update fire in sequence.

Moreover, presumably if you are testing to see if the statement is an update or an insert and doing different things depending now both update and insert will evaluate true... again something that your code may never have been designed to support.

Therefore, anyone designing triggers needs to consider MERGEs specifically.

And anyone working with a legacy system that has triggers needs to do a code review and maybe some testing before allowing MERGE statements.

DBMS_OUTPUT.PUT_LINE ('A' ||NULL||'B'); what will be the output

when you ran it, what did you see??

'AB'


ops$tkyte@ORA9IR2> exec DBMS_OUTPUT.PUT_LINE ('A' ||NULL||'B');
AB
 
PL/SQL procedure successfully completed.
 

Hi Tom,

My question is, how do I make a trigger behave like this.
My base table and trigger is in db1.
I have a database link into db2.
The trigger will insert data into db1 and db2 BUT if link
is down or inaccessible, insert will proceed only in db1.

Thanks.

Dear Tom,

I have seen some programmers use sql%notfound instead of sql%rowcount


update...
or
select...

if sql%notfound then
..

Surprisingly i found both works in same way or could you please tell us what is the difference between sql%notfound and sql%rowcount?

By the way i didnt understand your followup to Mikito Harakiri ("given the way databases work") could you please explain that in bit more detail? because i tried it shows 1 row updated for me!

Customer@Ibm> select * from t2 where x = 1;

X Y
---------- ----------
1 2

Customer@Ibm> pause

Customer@Ibm> update t2 set y = 6 where x = 1;
before update
after update

1 row updated.

Customer@Ibm>

Thanks and Regards,
Ibrahim.

Dear Tom,


1) update t2
..
if sql%rowcount = 0 then
insert into t2 ...
end if;
or

2) update t2
..
if sql%notfound then
insert into t2 ...
end if;

the above both are same??? which way is better and any particular reasons???

I always use the sql%rowcount! since some programmers using sql%notfound just a curiousity to know the difference!

Thanks & Regards,
Ibrahim.

Hi Tom,

A quick question following this thread.

create table tab1 (id int, name varchar2(20))
/

create table tab2 (id int, name varchar2(20))
/

create or replace trigger bu_tab1_trg
  after insert or update on tab1 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
declare
BEGIN
  IF (UPDATING ('name'))  THEN
    dbms_output.put_line('Update fired');    
    INSERT INTO tab2 VALUES (1, 'Update');  
  ELSIF (INSERTING) THEN
    dbms_output.put_line('Insert fired.');
    INSERT INTO tab2 VALUES (2, 'Insert');
  END IF;
end bu_tab1_trg;
/
create or replace procedure merge_tab (id_in NUMBER, name_in VARCHAR2) is
begin
 merge into tab1 a
 using (select id_in id, name_in name from dual) b
 on (a.id = b.id)
 when matched then
 update set a.name = b.name
 when not matched then
 insert (a.id, a.name)
 values (b.id, b.name);
end merge_tab;
/

--update fires on first call. not insert
SQL> exec merge_tab(1, 'one');

Update fired

PL/SQL procedure successfully completed

SQL> select * from tab1;

                                     ID NAME
--------------------------------------- --------------------
                                      1 one

SQL> select * from tab2;

                                     ID NAME
--------------------------------------- --------------------
                                      1 Update

Changing the trigger code from:

(UPDATING ('name')) to just (UPDATING) and it behaves correctly.

SQL> rollback;

SQL> exec merge_tab(1, 'one');

Insert fired.

PL/SQL procedure successfully completed

Strange how the insert section will never fire when using the column name. Is there any benifit to using the column name? Seems like any update even if you are not updating name causes the update portion of the trigger to fire. The standard I guess to check the value ourselves:

IF (UPDATING) then
    if :old.name ~= :new.name then
      --do this
    else 
      --do that
    end if;
end if;

or any version of that.

Thanks
 

You are correct. I guess I thought that since the table was empty to begin with that it HAD to be an insert. Technically I guess we are updating from null to a value. I added another column to the table, didn't change the trigger and only updated the new column. The update portion still fired and then I quickly realized that the merge statement has ALL columns in the when matched section so it was always updating every column listed there.

I tried seperate triggers as you suggested and its the same as stated in the beginning of the thread. Both insert and update triggers fire when using the merge. Looks like the only way to differentiate an update from an insert in a merge is to code an update/insert trigger and check for UPDATING or INSERTING without any column names. At least thats whats working for me.

Thanks Tom.

Hi,

I have database table MATCH_INFO with 50000 records, with a primary key 'MATCH_ID'

I want to create a databse trigger on this table which will insert/update a particular column (i.e. Fixture) of this table only on the insert/update of values in THE two columns (i.e. team_1st_id, team_2ND_id) of the same table..

To make that happen using Oralce Enterprise Manger Console or Oracle forms developer Object Navogator Schema options..I tried to put the code after INSERT AND DELETE of the two columns mentioned...

the following is the code in the triiger body text box of the form builder database trigger menu

BEGIN
UPDATE MATCH_INFO
SET
FIXTURE =
get_team_name(team_1st_id) || ' v ' || get_team_name(team_2nd_id)
WHERE MATCH_ID = MATCH_ID;
END;


it complies succesfully but the problem is that when i insert or update a value in the team_1st_id or team_2ND_id of a single record it updates all the rows in the table which this takes some time...

I want it to only insert/update the 'Fixture' column of the record whose value is changed or newly inserted..in the team_1st_id or team_2ND_id columns..

I would be grateful if someone helps..

here is the attached snapshot of my try..
</code> http://img326.imageshack.us/my.php?image=clip6br.jpg <code>

Hi

I want help in creating a databse trigger..

I have a table T1 with columns A1, B1
Column A1 is the primary key...

Another table t2 there are colums A2, B2
where column A2 is the primary key

note here A1 of Table T1 is equal to A2 of table T2
and B1 = B2

I want to do a delete operation on table T2 when ever a update occurs in B1 column of table T1 where A1 = A2

what would be the trigger code to achieve that??

thanks in advance

Hello Tom

I want to create a database tigger and need your help

I have two tables i.e. A and B

Table A has four columns
W
X
Y
Z


Table B has 10 columns of which four are:
L
M
N
O


both tables have the following relations, all the four columns in each table are jointly primary

key..

columns in Table A columns in Table B
W = L
X = M
Y = N
Z = O

I want to create a database trigger on Table A such that on every insert, update, delete of each

row in Table A, the row table B also gets inserted, updated or deleted respectively.

for example if I Insert a new record in table A with the following figures

W X Y Z
1 2 1 1201

then the same new record should be created in table b as
L M N O
1 2 1 1201


if I update a row on table A, such as
W X Y Z
1 2 1 2091

then the same row in table B should be updated
L M N O
1 2 1 2091


and lastly if I delete this record in Table A then the corresponding record in table B should also

be deleted..

I would appreciate if some one helps me to write the code for this..


regards

Tom
I have a question about triggers

create table test1
(
order_number number,
date_added date,
modified_by varchar2(10),
date_last_modified date
);
CREATE OR REPLACE TRIGGER tr_test1
BEFORE INSERT OR UPDATE ON test1
FOR EACH ROW
BEGIN
IF INSERTING THEN
:NEW.date_added := SYSDATE;
:NEW.modified_by := SUBSTR(USER,1,10);
:NEW.date_last_modified := SYSDATE;

ELSIF UPDATING THEN
:NEW.date_last_modified := SYSDATE;
:NEW.modified_by := SUBSTR(USER,1,10);
END IF;
END;
/



The thing is i need to be able to override the modified_by by passing a value...if no value is passed then
it should use the oracle user..Any ideas???

The problem is people can update the data..is there a way other than the default approach....??the procedure approach is the way to go...but is there any other way?

Hi Tom
Very helpful merge example thanks!
With regard to trigger syntax, I don't think I can but please can you confirm if I can break the syntax up and create a private proc in the header, like a standard procedure? Doing this would allow the body of the trigger to be very clear in what it is doing. The only way I can see of doing this is reference procedures that are externally declared and instead of doing this I'd like to keep it all in the trigger.

I'm using the following version:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production


I'm trying to create a package that contains a routine as follows:

create or replace procedure temp as
begin
merge into jciar_ifm_score_card_all t
using (select job_number, mtd_revenue, ytd_revenue, ctd_revenue
from jciar_ifm_score_card_all
where period = 'DEC-06'
and job_number like '-1%') t2
on (t.job_number = substr(t2.job_number, 3) AND t.period = 'DEC-06')
when matched then update set t.mtd_revenue = t2.mtd_revenue, t.ytd_revenue=t2.ytd_revenue, t.ctd_revenue=t2.ctd_revenue
when not matched then insert (job_number) values (null);
end;
/

SQL> @temp.sql

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE TEMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7 PLS-00103: Encountered the symbol "INTO" when expecting one of
the following:
:= . ( @ % ; <a SQL statement>

Can you point to me the mistake I'm making? When I use a SQLPlus tool, the same MERGE statement works fine.
I have hardcoded the PERIOD for simplicity sake. But the goal is to make it more dynamic.
Thanks