1
votes

DB2 Trigger before insert not working to delete a row in table

CREATE TRIGGER WDW.user_name_search_del
before INSERT ON WDW.user_name_search_b
REFERENCING NEW AS new
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
Delete from wdw.user_name_search_b
where user_name = new.user_name ;--

it is throwing the error

An unexpected token "END-OF-STATEMENT" was found following "name = new.user_name". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.9.78 SQL Code: -104, SQL State: 42601

1

1 Answers

0
votes

Two things to fix:

  1. Change "BEGIN ATOMIC" to just "BEGIN". DELETE is not supported in a "BEGIN ATOMIC" block
  2. Add "; END" before ";--" to end the "BEGIN" block.

Statement should be:

CREATE TRIGGER WDW.user_name_search_del 
  before INSERT ON WDW.user_name_search_b 
  REFERENCING NEW AS new 
  FOR EACH ROW 
  MODE DB2SQL 
BEGIN  
  Delete 
  from wdw.user_name_search_b 
  where user_name = new.user_name; 
END;