how define a trigger that use declare clause and use language sql

here is my trigger:

CREATE TRIGGER "SQLUser".SYM_ON_U_FOR_SYM_CHNNL_125123 AFTER 
UPDATE
   ON "SQLUser"."sym_channel" REFERENCING OLD ROW AS OLD NEW ROW AS NEW FOR EACH ROW 
   DECLARE @var_row_data VARCHAR(16336);
WHEN
   (
      1 = 1 
      and 1 = 1
   )
   -- I will use OLD/NEW alias here set @var_row_data = ''; LANGUAGE sql insert into mytable (id, data) values(1, @var_row_data);

here is error message:

ERROR: [SQLCODE: <-400>:<FATAL ERROR >] [Location: ] [%msg: <ERROR #5475: Compile routine error: User.symchannel. 错误: %Library.CacheTrigger.cls(Execute) 来自成员 'SYMONUFORSYMCHNNL125123:User.symchannel' 行 'Execute+6' 的生成代码的方法 Trigger : SQLCODE=-1 : 需要标识符,找到@^DECLARE @ User.symchannel.cls(SYMONUFORSYMCHNNL125123) 来自成员 'SYMONUFORSYMCHNNL125123' 的方法 Trigger : SQLCODE=-1 : 需要标识符,找到@^DECLARE @>] Error Code: 400 Query = CREATE TRIGGER "SQLUser".SYM_ON_U_FOR_SYM_CHNNL_125123 AFTER UPDATE ON "SQLUser"."sym_channel" REFERENCING OLD ROW AS OLD NEW ROW AS NEW FOR EACH ROW DECLARE @var_row_data VARCHAR(16336); WHEN (1=1 and 1=1) -- I will use OLD/NEW alias here set @var_row_data = ''; LANGUAGE sql insert into mytable (id, data) values(1, @var_row_data);

  • 0
  • 0
  • 49
  • 0
  • 1

Answers

Triggers expressed in SQL cannot contain a DECLARE clause, but you'd get a lot more flexibility when using ObjectScript triggers. See the reference documentation for more details.