:NEW.variable

[deulyd]

Hi,

In a trigger using the :NEW, is there a way to access the value of :NEW with a variable instead of a fieldName.

Ex :

x := 'FLD1';
:NEW.x

-- instead of doing this :NEW.FLD1;

Thanks

Daniel

 

[SantaMufasa]

Deulyd, the ":new..." and "ld..." references are to the new and old contents of the columns in the trigger table. So, you really have no latitude in the naming of the :new and ld column references. But, by using assignments (e.g. "x := :new.<column>" and ":new.<column> := x"), you should be able to do whatever you want.

Let us know either if this resolves your need or what your need is for "renaming" the expression references.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[deulyd]

Hi Mufasa,

Its not what I meant... Its just that I need to store new values in an XML string when updating, so I want to check every field values in the :NEW to exclude NULLS.

So I create a cursor to get all fields names of the table then I want to access :NEW.sFieldName to verify if null­. I dont want to hardcode all the field names since I need to do that for 400 triggers... :S

Thanks

Daniel

 

[Thargy]

deulyd,

in your trigger definition, you can use the syntax

CREATE OR REPALCE TRIGGER mytable_update
    AFTER UPDATE
    ON mytable
    REFERENCING OLD AS deulyd_var_name NEW AS deulyd_var_name2
    FOR EACH ROW
BEGIN
   --Put your code here, making reference to
   --the "variable" names defined above

END;

The other thing is that I noticed from your post that you're using this to check for nulls. Is there a good reason for not imposing a not null constraint on the relevant table column?

Regards

Tharg

Grinding away at things Oracular

 

[deulyd]

Again, that's not it... :/

lets say you got the FieldName inside a variable (sFldName) and you want to access the :NEW value for that field. Is there any way to call something like :

x := :NEW.sFldName

Thanks

Daniel

 

[Dagon]

I'm not sure how having an alias for the column will help when every table is different and has different numbers of columns. Why don't you generate the code for the triggers using a SQL from SQL technique ? It would be relatively easy to spool out something like:

select 'if :new:'||column_name||' is null then ..'
from all_tab_columns
where table_name = <table_name>

If the triggers are all virtually identical, you could probably write a PL/SQL procedure which would generate them for you.