Problem with auditing trigger on Oracle db

by Moamen

i create trigger on oracle DB for auditing insert or update, when i check nre value if exists it gives error that is null REF.

select case item_no when :new.item the flag :=1 else flag :=0 end from items

It's not entirely clear what you are doing but just looking at your SQL statement we can see that it would give an error as it stands.

We'll assume that you have managed to create a PL/SQL trigger on the tables you want to audit. If not refer to these Q & As
We'll assume also that you're audit table is called items and that the trigger successfully inserts data into table. Now all that's left to do is to extract data from the table - which is the easy part!

The problem with your query as it stands

select case item_no when :new.item the flag :=1 else flag :=0 end from items

is that you're mixing Oracle PL/SQL and SQL and referring to objects which can only be referenced in triggers (:new.item). This is why the null REF error occurs.

Your SQL statement should look like this select case item_no when then 1 else 0 end flag from items

where evaluates to a constant (of the same type as the item_id column in the items table) such as 'INSERT'.

With either form of the SQL CASE statement you can't assign a value to anything programmatically, the assignment is implicit. In fact this applies to all SQL statements. Refer to the Oracle SQL Language Reference for the syntax of CASE statements.

For more help, see our series of Oracle SQL tutorials and our series of PL/SQL tutorials.

For information about training courses both on-line and classroom-based see our Oracle training page.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.