插入前的DB2触发器

Question

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 ;--

在“name=new.user_name”后面发现意外的标记“end-of-statement”。预期的令牌可能包括：“”..sqlcode=-104,sqlstate=42601,driver=4.9.78 SQL code:-104,SQL state:42601

Answer 1

需要解决两个问题：

1. 将“begin atomic”更改为“begin”。“Begin Atomic”块不支持删除
2. 在“；--”之前添加“；end”以结束“begin”块。

语句应为：

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;