Reputation: 15
Table:
CREATE TABLE EXAMPLE1 (column1 integer, column2 integer, column3 integer);
Insert:
INSERT INTO EXAMPLE1 VALUES (1,11,111);
Trigger:
create or replace TRIGGER trigger_name
BEFORE UPDATE OF COLUMN1 ON EXAMPLE1
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'You can't directly update this column.');
END;
Update:
UPDATE EXAMPLE1 SET COLUMN1 = 2 WHERE COLUMN2 = 11;
So, when I try to update the column1 (column1 = 2) trigger should execute and tell me that I can't directly change that column, but if I execute a query like this:
UPDATE EXAMPLE1 SET column1 = 1, column2 = 22 where column3 = 111;
I mean where I send also column1 = 1 (column1 is equal to 1) so there is no changing of value, it should update the table because I'm changing just column2, column1 stays unchanged equal to 1.
Upvotes: 1
Views: 1371
Reputation: 35900
Use when
clause as follows:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE UPDATE OF COLUMN1 ON EXAMPLE1
FOR EACH ROW
WHEN ( NEW.COLUMN1 <> OLD.COLUMN1
OR NOT (NEW.COLUMN1 IS NULL AND OLD.COLUMN1 IS NULL)
)
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'You can''t directly update this column.');
END;
/
Upvotes: 0
Reputation: 24568
you are still updating the column no matter what the new value is , in your trigger you have to check if the value is changing or not :
CREATE OR REPLACE TRIGGER trigger_name
BEFORE UPDATE OF COLUMN1 ON EXAMPLE1
FOR EACH ROW
BEGIN
IF DECODE(:new.column1, :old.column1, 1, 0) = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'You can''t directly update this column.')
END IF
END;
Upvotes: 2