DB2 Trigger before insert

Question

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

Wen-Yi Chua Wen-Yi Chua · Accepted Answer · 2018-10-13T15:24:13

Two things to fix:

Change "BEGIN ATOMIC" to just "BEGIN". DELETE is not supported in a "BEGIN ATOMIC" block
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;

DB2 Trigger before insert

1 Answers