-1

I'm trying to insert a record into a table that's identical to the record that was changed in another table, using a trigger.

I also need to insert whether the operation was an insert, update or delete

CREATE OR REPLACE TRIGGER triggername
AFTER INSERT OR UPDATE OR DELETE
ON tablename

DECLARE
operation_type VARCHAR2;
changed_id VARCHAR2;
BEGIN

IF UPDATING
THEN operation_type := 'Update';

ELSE IF INSERTING
THEN operation_type := 'Insert';

ELSE IF DELETING
THEN operation_type := 'Delete';

END IF;


SELECT id
FROM tablename
WHERE :OLD.record1 != :NEW.record1
OR :OLD.record2 != :NEW.record2
OR :OLD.record3 != :NEW.record3
OR :OLD.record4 != :NEW.record4
OR :OLD.record5 != :NEW.record5
OR :OLD.record6 != :NEW.record6;

INTO changed_id;

  INSERT INTO trigger_table.id VALUES(changed_id);
  INSERT INTO trigger_table.type VALUES(operation_type);
END;
/

I'm getting an error

"BAD BIND NAME :NEW" or "BAD BIND NAME :OLD" for each of the :NEW or :OLD

shown above

Steve Robinson
  • 11
  • 2
  • You could make reference to `:OLD` and `:NEW` values only in a **ROW LEVEL TRIGGER**. You must use `FOR EACH ROW`. – Lalit Kumar B May 13 '15 at 04:55
  • Triggers don't support syntax to test the whole record, You need to test on a column by column basis. – APC May 13 '15 at 05:22
  • Have a look at the answer to [this similar SO question](http://stackoverflow.com/q/26950413/146325) on auditing triggers. – APC May 13 '15 at 05:27

2 Answers2

1

There are multiple issues with your trigger code:

  1. operation_type VARCHAR2;

You must declare the variable size. else you would get an error

PLS-00215: String length constraints must be in range (1 .. 32767)

For example, modify it as:

operation_type VARCHAR2(20);
changed_id VARCHAR2(20);
  1. SELECT .. INTO statement
SELECT id
FROM tablename
WHERE :OLD.record1 != :NEW.record1
OR :OLD.record2 != :NEW.record2
OR :OLD.record3 != :NEW.record3
OR :OLD.record4 != :NEW.record4
OR :OLD.record5 != :NEW.record5
OR :OLD.record6 != :NEW.record6;

INTO changed_id;

It is syntactically incorrect. INTO clause comes should be between SELECT and FROM clause:

SELECT id 
  INTO changed_id 
FROM tablename 
 WHERE ...
  1. WHERE :OLD.record1 != :NEW.record1

This is your original question regarding bad bind variable error.

To make reference the :OLD and :NEW values, you need to create ROW LEVEL Trigger which must include the following condition:

FOR EACH ROW
  1. INSERT statements
INSERT INTO trigger_table.id VALUES(changed_id);
INSERT INTO trigger_table.type VALUES(operation_type);

You must insert a single row and not two rows with two insert statements. Also, trigger_table.id is incorrect syntax. You need to mention the column names within parenthesis.

For example,

INSERT INTO trigger_table(column1, column2) VALUES
          (changed_id, operation_type);
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0

When you are trying to use :NEW for delete and :OLD for create in trigger, you would see these errors.

It would be better to move the select & insert statements inside

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;
IF DELETING THEN ... END IF;

to use :NEW and :OLD variables

S.Krishna
  • 868
  • 12
  • 26
  • I'm not sure I follow. The :NEW and :OLD statements are meant to identify which record within the table was changed. Is there any easier way to figure out which record was changed? – Steve Robinson May 13 '15 at 04:03
  • You are right. But since your trigger treats all the create, update and delete transactions the same and tries to run the select and insert statements, you are seeing the error. instead of having the select and insert outside of 'if creating' and 'if deleting' constructs, move them inside. When a record is created there is no value in :OLD and when the record is deleted there is no value in :NEW – S.Krishna May 13 '15 at 04:07
  • No, this is not the reason for the bad bind variable error. The reason is that to make reference to the `:OLD` and `:NEW` values, it should be a **ROW LEVEL** trigger, which needs the `FOR EACH ROW` condition. – Lalit Kumar B May 13 '15 at 05:07