Reproducing an Oracle BEFORE trigger in an INSTEAD OF trigger

[Simon Blais]

Hi!

I've read everywhere that there is nothing in an Oracle BEFORE trigger that
can't be done in an INSTEAD OF trigger...

So I hope someone can help me on that one:

Suppose I have a table with varchar fields A, B, C and FORMATTED_COL_D.
Column FORMATTED_COL_D is the result of a user-defined function that uses
the values of columns A and B. To do so, I have a BEFORE trigger like this
one:

CREATE TRIGGER trg_format BEFORE INSERT OR UPDATE
ON TABLE MY_TABLE FOR EACH ROW
BEGIN
:new.formatted_col_d = my_func(a, b)
END;

My table has much more columns than in this example and since it is still in
development, it can (and it will) change anytime. Enumerating each column in
an INSERT or UPDATE command is out of the question.

How can I do this in an INSTEAD OF trigger?

Thanks for your help

Simon.

[Dan Guzman]

IMHO, a better solution would be to avoid the trigger entirely and use a
computed column based on the function If you must use am INSTEAD OF
trigger without a column list:

CREATE TRIGGER TR_MYTABLE
ON MYTABLE
INSTEAD OF INSERT, UPDATE
AS

SET NOCOUNT ON

SELECT *
INTO #MYTABLE
FROM inserted

UPDATE #MYTABLE
SET FORMATTED_COL_D = dbo.my_func(A, B)

INSERT INTO MYTABLE
SELECT * FROM #MYTABLE

GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Simon Blais" <sbl...@pgsystem.com> wrote in message
news:ORin9.1270$%21....@charlie.risq.qc.ca...

[Simon Blais]

Looks like a good workaround. I will try that.

Thanks.

Simon.

"Dan Guzman" <dang...@nospam-earthlink.net> a écrit dans le message de
news: zRFn9.10696$lV3.1...@newsread1.prod.itd.earthlink.net...