Home » SQL & PL/SQL » SQL & PL/SQL » SET DEFAULT in a trigger
SET DEFAULT in a trigger [message #214739] Wed, 17 January 2007 19:21 Go to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can use UPDATE table SET col = DEFAULT to set the value of a column to its Default value.

But that means you have to ASK Oracle to do it. I want the column to be defaulted on (almost) ANY update. Ideally, I would get a trigger to do this:

CREATE OR REPALCE TRIGGER trigname
BEFORE UPDATE ON tabname
FOR EACH ROW
WHEN (old.col1 = new.col1)   -- ie. user is not updating col1, so the trigger will reset it.
BEGIN
    :new.col1 := DEFAULT;
END;
/

This syntax is of course invalid: col1 := DEFAULT; is not a legal command.

Is there a way to do it that I cannot find in the doco or Google?

My fallback is to log the updated rows in a GTT with a trigger, and then perform an UPDATE SET DEFAULT WHERE ROWID IN (.. gtt ...) in an AFTER UPDATE statement level trigger, a-la-Mutating Table work-around.

Ross Leishman
Re: SET DEFAULT in a trigger [message #214772 is a reply to message #214739] Thu, 18 January 2007 00:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
A work around.
I only not feeling it is a good one.
Getting the data_default from user_tab_columns.
But it is a LONG column and a performance issue too, as we need to fetch that for each row.

By
Vamsi
Re: SET DEFAULT in a trigger [message #214774 is a reply to message #214772] Thu, 18 January 2007 00:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I considered that also, and then discarded it as messier and less performant than my other work-around.
Re: SET DEFAULT in a trigger [message #214778 is a reply to message #214774] Thu, 18 January 2007 01:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If your default-values don't change over time, you can generate the trigger based on the values of the defaults.
Generate col1 := <the actual default value>
Re: SET DEFAULT in a trigger [message #214970 is a reply to message #214778] Thu, 18 January 2007 15:48 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Good idea, thanks.
Previous Topic: PROBLEM WITH AFTER INSERT TRIGGER (DATA NOT THERE YET!!)
Next Topic: Generate Report
Goto Forum:
  


Current Time: Fri Dec 09 09:39:49 CST 2016

Total time taken to generate the page: 0.07462 seconds