Using INSERT INTO SELECT in a TRIGGER

[sujosh]

I get "a trigger created with compilation errors" when I try to execute this. Any help is appreciated!! Where am I going wrong please help.

Thanks

CREATE TRIGGER AAA
AFTER
UPDATE ON B

FOR EACH ROW

insert into B
select i.user_id,i.ticket_seq,i.printed_date,i.signed_date,i.created_by,i.created_on,i.updated_by,i.updated_on,i.ticket_type,
i.ticket_code,i.src_location,i.dest_location,i.src_user_id,i.dest_user_id,i.custodian_xfer,i.src_site_id,i.src_aisle_bin,i.dest_site_id,i.dest_aisle_bin
from NEW i,OLD d
where i.user_id = d.user_id and i.ticket_seq = d.ticket_seq and d.signed_date is null
and i.signed_date is not null
/

 

[avjoshi]

you need the encase your insert statement into a BEGIN..END block.

Anand.

 

[sujosh]

Hi Anand,

I did try enlcosing in a Begin ..ENd block and am still getting the same error. Any other ideas?

Thanks

 

[avjoshi]

what is it that you want to acompolish? Your trigger statement will not work for many reasons. Most important is the reference to new and old is not corect.
If you tell what are you trying to acompolish we can help you better.

Anand.

 

[SantaMufasa]

Sujosh,

Assuming you are using SQL*Plus, after the attempted compilation, and Oracle responds with "a trigger created with compilation errors", could you please (at the SQL*Plus prompt) enter:

SQL> show errors

...then copy and paste the results to this thread?

Thanks,

Dave
Sandy, Utah, USA @ 21:48 GMT, 14:48 Mountain Time

 

[sujosh]

Hi Dave,

I have some basic questions on Oracle.

1. Are there any magic tables quivalent to insert and deleted (MSSQL)?
2. Is my syntax right?

Here is the errors I get swhen I show errors. Thanks for your time

SQL> CREATE TRIGGER XXX
2 AFTER
3 UPDATE ON B
4
5 FOR EACH ROW
6
7 Begin
8 (insert into C
9 select i.user_id,i.ticket_seq,i.printed_date,i.signed_date,i.created_by,i.created_on,i.upda
ted_by,i.updated_on,i.ticket_type,
10 i.ticket_code,i.src_location,i.dest_location,i.src_user_id,i.dest_user_id,i.custodian_xfer,
i.src_site_id,i.src_aisle_bin,i.dest_site_id,i.dest_aisle_bin
11 from NEW i,OLD d
12 where i.user_id = d.user_id and i.ticket_seq = d.ticket_seq and d.signed_date is null
13 and i.signed_date is not null) ;
14 END;
15 /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER UPD_FAT_TRIG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
The symbol &quot;(&quot; was ignored.

7/34 PLS-00103: Encountered the symbol &quot&quot; when expecting one of the

LINE/COL ERROR
-------- -----------------------------------------------------------------
following:
; return returning and or group having intersect minus start
union where connect

 

[carp]

OK - like the message says, get rid of the parentheses:

CREATE TRIGGER XXX
AFTER UPDATE ON test
FOR EACH ROW
Begin
insert into C
select i.user_id,i.ticket_seq,i.printed_date,i.signed_date,i.created_by,i.created_on,i.updated_by,
i.updated_on,i.ticket_type,
i.ticket_code,i.src_location,i.dest_location,i.src_user_id,i.dest_user_id,i.custodian_xfer,
i.src_site_id,i.src_aisle_bin,i.dest_site_id,i.dest_aisle_bin
from NEW i,OLD d
where i.user_id = d.user_id and i.ticket_seq = d.ticket_seq and d.signed_date is null
and i.signed_date is not null ;
END;

Also, I would suggest renaming your tables to something besides NEW and OLD; these words have special meanings in the context of triggers!

Elbert, Colorado
1555 MST

 

[SantaMufasa]

Anand,

Actually, Sujosh's table references of &quot;NEW&quot; and &quot;OLD&quot; are legal.

Sujosh,

Have you received a &quot;clean&quot; compile yet from your trigger code? If not, please post your error message(s) again.

Dave
Sandy, Utah, USA @ 23:32 GMT, 16:32 Mountain Time

 

[carp]

Dave -
How right you are and how myopic I am!
I think eliminating the parentheses will do the trick.

 

[SantaMufasa]

Sujosh,

Also, regarding your other question: &quot;Are there any magic tables quivalent to insert and deleted (MSSQL)?&quot;

My apologies for not being more MSSQL literate; I don't know that those &quot;tables&quot; do in MSSQL. But Oracle PL/SQL does support the named conditional variables, INSERTING, UPDATING, and DELETING, which you can use in trigger code to direct program logic as follows:

Begin
IF INSERTING then statements;
ELSIF UPDATING then statements;
ELSIF DELTING then statements;
END IF;
END;
/

So, even if these named conditionals do not relate to the MSSQL functionality to which you refer, they still are probably useful for you to know for your trigger writing.

Cheers,

Dave
Sandy, Utah, USA @ 23:59 GMT, 16:59 Mountain Time

 

[sujosh]

Dave,


Here is the stuff that I ahve found out so far. The tables inserted and deleted are temp tables that store the old and the new values in SQLServer. And hence I was trying to use that same kinda of logic saying the NEW and OLD (as tables equivalent to Inserted and deleted IN Sql Server). But you are right it does not look like I can do it that way. I have now rewritten my trig logic as follows.

My objective
============

1. There are 2 tables i.e A and A-history
2. I want to move the record from A to A-history when a date field is populated (from null to a valid date in my app)
3. Delete the current row from table A.

The code below is scucessfulyl compiled and created. But when I try and update I get this error

<<<<

ORA-04091: table A is mutating, trigger/function may not see it
ORA-06512: at &quot;ATRIG&quot;, line 13
ORA-04088: error during execution of trigger 'ATRIG'


>>>>>

DROP TRIGGER Atrig
/

CREATE TRIGGER Atrig
AFTER
UPDATE ON A
FOR EACH ROW

Begin

If NEW.user_id = :OLD.user_id and :NEW.ticket_seq = :OLD.ticket_seq and :OLD.signed_date is null and :NEW.signed_date is not null ) Then

insert A_history
(user_id,ticket_seq,printed_date,signed_date,created_by,created_on,updated_by,updated_on,ticket_type,
ticket_code,src_location,dest_location,src_user_id,dest_user_id,custodian_xfer,src_site_id,src_aisle_bin,dest_site_id,dest_aisle_bin)
values
NEW.user_id,:NEW.ticket_seq,:NEW.printed_date,:NEW.signed_date,:NEW.created_by,:NEW.created_on,:NEW.updated_by,:NEW.updated_on,:NEW.ticket_type,
:NEW.ticket_code,:NEW.src_location,:NEW.dest_location,:NEW.src_user_id,:NEW.dest_user_id,:NEW.custodian_xfer,:NEW.src_site_id,:NEW.src_aisle_bin,:NEW.dest_site_id,:NEW.dest_aisle_bin);

END If;
delete A
where ticket_seq = :NEW.ticket_seq and :OLD.signed_date is null and :NEW.signed_date is not NULL;

End;

/

<<<<<<<< End of code >>>>>>>>>

Now my questions is I found out that I can use

PRAGMA AUTONOMOUS TRANSACTION and a Commit in the trigger. DO you know how I can do this?

P.S I have also tried doing an after update on table A and doing after insert on table A-history(doing the delete stuff of table A) and still it is mutating on able A becasue it is in the same LUW

Thanks for any help!!

 

[sem]

I'd suggest you to redesign your application and issue normal deletes instead of updates. Though if you don't want it, you may handle your case by creating some temporary storage for rows being updated (temporary table or package variable), populate it from row-level trigger and then process them (in that IMO strange manner) in after-update statement-level trigger. You can not use AUTONOMOUS TRANSACTIONs, because during update the record is locked by the main transaction, thus it can not be deleted.

Regards, Dima

 

[sujosh]

Dima,

Thanks for your input. Since I am not familiar with package variables/temp table to be created from a trigg could you please elaborate or can you give me some place where I can find this kind of info. Thanks much!

 

[dbtoo2001]

Isn't a mutating table when the trigger is attempting to update the table on which it just fired?
From Metalink:
A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

In this case, the trigger is fired on Table A, then attempts to do a delete from A.

 

[anniez]

I have an update/insert trigger that needs some help.

before insert on table
referencing old as old new as new
for each row
begin

update tableb
set
a=:new.a
b=:new.b
etc
where id+:new.id and new:code=1 or :new.code=2)

if sql%rowcount=0 then
insert
.....
end if;
end;

How do I update if id=:new.id and new.code=3 or :new.code=4) ?

Should it be an if-then after update? something like
if (code=1 or code = 2) then
set...
else
if code =3
set other stuff
end if;