0

I am writing an INSTEAD OF UPDATE trigger and I want to identify what columns has been given to the WHERE clause of the UPDATE statement that triggers the trigger.

For example,

Let's say that we have the table below

table_name
--COL1
--COL2
--COL3
--COL4

I want, when an update is performed e.g.UPDATE table_name SET COL1=VAL1,COL2=VAL2 WHERE COL3=VAL3

to be able to say in my trigger

CREATE or replace TRIGGER DEVICES_VIEW_TR 
  INSTEAD OF UPDATE ON DEVICES_VW
  BEGIN
    IF (COL3 has been given in the where clause) THEN
      variable=getValueOf(COL3);
    ELSEIF (COL4 has been given in the where clause) THEN 
      variable=getValueOf(COL4);
    END IF;
  END;
/

Can this be done?

Thanks

NikosDim
  • 1,398
  • 2
  • 21
  • 40
  • 1
    That doesn't look good. Rather than changing col1 and col2 which are mentioned in the set clause you want to change col3 mentioned in the where clause? That will be very confusing. Anyhow, triggers cannot tell you the firing statement, hence no chance to know about the where clause. – Thorsten Kettner Oct 25 '13 at 09:17
  • @ThorstenKettner I don't want to change the value of the COL3 mentioned in the where clause. I just want to know if the update statement that fired up the trigger contained the COL3 or the COL4 in the where clause. The reason for this is because I want to execute updates that the where clause contains only a primary key column and not a column that will update more than one records. I hope it is clear now. – NikosDim Oct 25 '13 at 09:30
  • 1
    Okay, so your example is a bit confusing. As I said, triggers don't tell you the triggering statement. So you cannot tell wether col3 or col4 were in the where clause. Your only option would be to detect updates affecting more than one row and throw an exception in case. Even this is not easily done, as there is no information available in triggers about the number of affected rows either. Look here for a way to determine the number of rows affected: http://stackoverflow.com/questions/8770386/how-to-get-number-of-rows-affected-by-a-statement-when-inside-that-statements-t – Thorsten Kettner Oct 25 '13 at 10:43
  • I edited my question to be more clear. Thanks for the info. I ll take a look on what you have suggested. – NikosDim Oct 25 '13 at 14:08
  • What are you doing with `variable`? – Sebas Oct 31 '13 at 06:46
  • @Sebas I want to use it in a where clause of a select statement. – NikosDim Nov 01 '13 at 13:24

3 Answers3

1

You can use the UPDATING('column name') in your trigger:

-- in INSTEAD OF trigger body:
IF updating('COL1') THEN
  -- some operation
END IF;

Check this for an example: Example of using UPDATING

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41
  • 1
    I already know this but this is only on checking what is being passed in the SET part of the UPDATE right? So it is not what I am looking for – NikosDim Oct 25 '13 at 09:31
  • @NikosDim Wait, you want the get the information which columns were in the `WHERE` clasue? I don't think this is possible in any way. I'm sorry I didn't grasp your intent from your description. – Przemyslaw Kruglej Oct 25 '13 at 09:37
0

You could use the NEW and OLD pseudorecords and run a comparison of the values

if :NEW.COL3 <> :OLD.COL3 THEN ...
Mike
  • 2,391
  • 6
  • 33
  • 72
0

Triggers don't know anything about the statement that invoked them, so you'll have to use some kind of out-of-band signalling, e.g. change your application to set some globals in a database package, or use an application context.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158