0

I am trying to eliminate null values without making the column "not null" to avoid errors. So, I wanted to create a trigger to update each inserted row if it contains null to replace it with 'N'. The following syntax results in

"Executed as Single statement. Failed [5423 : HY000] This trigger contains an invalid REFERENCING clause. Elapsed time = 00:00:00.018 STATEMENT 1: REPLACE failed. "

The Syntax:

Replace  TRIGGER DB.C_UP_CLIENTS
AFTER INSERT ON DB.CLIENTS
REFERENCING OLD table as old_clients_table
 NEW table  as new_clients_table
 FOR EACH Statement
(update DB.CLIENTS set NEEDS_AUTHENTICATION = 'N' where NEEDS_AUTHENTICATION is null;);
Seif Ahmed
  • 1
  • 1
    You can't have an OLD TABLE reference in an INSERT trigger, and you should use the NEW TABLE alias in the trigger body, not the target table itself. But your description sounds like a BEFORE INSERT ROW trigger with WHEN / SET may be more applicable. – Fred Nov 23 '21 at 18:30

1 Answers1

0

As Fred said, we don't have the OLD table for an INSERT TRIGGER. I would do this as follows (in an BEFORE INSERT trigger):

REPLACE TRIGGER DB.C_UP_CLIENTS ENABLED 
BEFORE INSERT ON DB.CLIENTS
REFERENCING NEW AS NEW_VALUE
FOR EACH ROW
BEGIN ATOMIC(
    SET NEW_VALUE.NEEDS_AUTHENTICATION =coalesce(NEW_VALUE.NEEDS_AUTHENTICATION ,'N');
    )
END;
tinazmu
  • 3,880
  • 2
  • 7
  • 20