Re: Oracle trigger equivalent of MSSQL trigger

Eric Patrick wrote:

> I am porting a system from MSSQL to Oracle. Part of that system
> includes the following scenario:
>
> Tables: Event, EventQueue, MyTable
> Trigger: MyTableEventInsert
>
> The Event table contains rows that define web services to call. It
> contains (among other things) a column called AppliesToTable.
>
> The MyTable table can be anything, but the idea is to check what
> events apply to MyTable inserts, and add a row to EventQueue for each
> event that applies to a MyTable inserted row.
>
> The EventQueue is thus automatically populated by the
> MyTableEventInsert trigger, and can be processed by application code
> accordingly.
>
> The MSSQL trigger works like a champ, and is constructed along these
> lines:
>
> CREATE TRIGGER MyTableEventInsert ON MyTable
> FOR INSERT
> AS
> INSERT INTO EventQueue (...)
> SELECT (...)
> FROM Inserted
> INNER JOIN Event
> ON Event.AppliesToTable = 'MyTable'
>
> I would like to create the equivalent for Oracle.
>
> My question is this: can I treat Oracle's NEW / OLD as logical
> tables, or do I need to process it along these lines:
>
> CREATE OR REPLACE TRIGGER MyTableEventInsert
> AFTER INSERT
> ON MyTable
> FOR EACH ROW
>
> INSERT INTO EventQueue (...)
> SELECT (...)
> FROM MyTable
> INNER JOIN Event
> ON Event.AppliesToTable = 'MyTable'
> WHERE MyTable.MyTableID = :new.MyTableID
>
> Lastly, if I do it like my statement above, do the Oracle DBAs out
> there cringe at the performance implications? (This stikes me a much
> more cursor oriented than a nice, tight relational join.)
>
> Thanks in advance,
>
> Eric

You should be able to use Oracle's Advanced Queueing. A couple of full demos can be found at:
http://www.psoug.org/reference/library.html Scroll down to "DBMS_AQ Built-in Package"

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)