Please help me check trigger error

ximen — Fri, 15 Dec 2023 11:47:36 GMT

When the updated value contains a comma, the trigger inserts the original column value,

CREATE TABLE "DBA"."test_split" (
    "UUID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "goods" VARCHAR(90) NULL,
    "price" DECIMAL(111,3) NULL,
    "OLD_uuid" INTEGER NULL,
    CONSTRAINT "ID" PRIMARY KEY ( "UUID" ASC )
) IN "system";
COMMENT ON TABLE "DBA"."test_split" IS '
';
--insert demo data
INSERT INTO "DBA"."test_split" ("UUID","goods","price","OLD_uuid") VALUES(1,'apple',10.000,NULL)；
--create trigger where update 
CREATE TRIGGER "update_trigger" AFTER UPDATE OF "goods"
ORDER 1 ON "DBA"."test_split"
  REFERENCING OLD AS old_test_split NEW AS new_test_split
FOR EACH ROW  WHEN(  CHARINDEX(',', new_test_split.goods)>0)   -- Including commas to trigger business execution
BEGIN
     declare @old_uuid INTEGER ;
     declare @old_goods varchar(100);
     declare @old_price  int;
     declare @instert_new_goods varchar(100);
     select  uuid,goods,price into @old_uuid,@old_goods,@old_price  from test_split where Old_test_split.uuid=test_split.uuid ;
  select right(goods,len(goods)-CHARINDEX(',',goods)) into @instert_new_goods from  test_split where new_test_split.uuid=test_split.uuid; 
  --Replace Update
update  test_split set goods=@instert_new_goods from  test_split where new_test_split.uuid=test_split.uuid; 
--Reinsert old data and record uuid
INSERT INTO "DBA"."test_split" ("goods","price","OLD_uuid") 
VALUES(@old_goods,@old_price,@old_uuid)
END;

--run:

update test_split set goods='apple,test',price=20 where uuid=1;

UUID   goods     price    old_uuid
1      test       20        
2     apple       10      1

Re: Please help me check trigger error

chris_keating — Sat, 16 Dec 2023 12:16:36 GMT

I would debug this with the Stored Procedure debugger in SQL Central.

Navigate to the trigger source in SQL Central
Select Mode | Debug
Set a breakpoint after the first select
Execute the update

Examine the value set for the "@old__goods_". It is the values currently in the table - the update has completed by the time this trigger is fired. You should access the old values using Old_test_split.

Re: Please help me check trigger error

Baron — Sun, 17 Dec 2023 04:36:12 GMT

can you post what is the expected output?

When I execute your same code, I get different output as in your last block.

My --run looks so:

UUID    goods            price    OLD_uuid
1       'test'           20.000
2       'apple,test'     20.000   1

Question Re: Please help me check trigger error in Technology Q&A

Please help me check trigger error

Re: Please help me check trigger error

Re: Please help me check trigger error