Re: Converting MSSQL trigger stuff to Oracle???

Hi Brad...
I think this ios what you want.
CREATE OR REPLACE TRIGGER colprint_audit_info BEFORE INSERT OR UPDATE ON "COLPRINT"
FOR EACH ROW
BEGIN
IF INSERTING THEN
:new.user_name := user;
:new.lastedit := sysdate;
ELSIF UPDATING THEN
:new.user_name := user;
:new.lastedit := sysdate;
END IF;
END;
;

Where colprint is the table name....
This trigger fires only when insert and update occur.. Populates a user field with the current user name and the date field with the current system time.

WIll still fire if the record is not commited but does not require use a seperate transaction....

Don't need to check row count this is what your second question does on SQL Server...(Without the user field)

In article <hUFX2.30$Mw2.52091_at_client.news.psi.net>,   "Brad Pybus" <brad_pybus_nospam_at_hotmail.com> wrote:
>
> I'm new to Oracle and new to SQL server. I need to convert some SQL
Server
> trigger stuff to Oracle.
>
> I want to be able to do the following in Oracle:
>
> The trigger will be AFTER Updates and Inserts but I do not want to
process
> the trigger if no rows
> were actually updated or inserted. How do I do this in Oracle?
>
> IF @@rowcount > 0
> ............
> END
>
> Can I use sql%rowcount? I seemed to get some indications from another
post
> that this won't work.
>
> --------
> NEXT:
>
> If I have a trigger on TABLEA for insert, how do I convert the
following to
> Oracle:
>
> UPDATE TABLEA
> SET MyDateField = getdate() FROM inserted
> WHERE TABLEA.PrimaryKey = inserted.Primarykey
>
> What baffles me is that I do not even understand the MS SQL being used
here
> (I don't have an SQL reference handy). I assume "inserted" is some
sort of
> internal cursor that contains all the rows to be inserted. I do not
see
> the point of the where clause. It seems to be saying... "only look
at the
> records that are already in TABLEA". If rows are being inserted, how
can
> they already be in TABLEA?
>
> --
> To reply to me, remove the "_nospam" from my address.
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Fri May 14 1999 - 07:00:49 CDT