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

asked 15 Dec '23, 11:47

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

edited 16 Dec '23, 06:44

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
(17 Dec '23, 04:36) Baron

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.

permanent link

answered 16 Dec '23, 12:16

Chris%20Keating's gravatar image

Chris Keating
7.7k49127
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79

question asked: 15 Dec '23, 11:47

question was seen: 161 times

last updated: 17 Dec '23, 04:36