Attachment: test.zip [ 11541 ]

description: Create the following database/trigger, and update the single record in the single table. You'll receive the message "Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
action cancelled by trigger (0) to preserve data integrity."

CREATE DATABASE 'C:\test.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET NONE;

CREATE TABLE NEW_TABLE (
NEW_FIELD NUMERIC(9,2)
);

INSERT INTO NEW_TABLE (NEW_FIELD) VALUES (4);

SET TERM ^ ;

CREATE OR ALTER TRIGGER NEW_TRIGGER FOR NEW_TABLE
ACTIVE AFTER UPDATE POSITION 20
AS
-- Typically this variable would be used in the trigger. To make it simple, I've
-- removed all code possible to still get the error.
declare variable NewHours numeric(9,2) = 0;
BEGIN
-- Remove the coalesce and the update will work.
-- Remove the multiplication and the update will work.
-- Remove this unneeded variable and the update will work.
NewHours = coalesce(new.new_field * (0), 0);

-- Remove this exit command and the update will work. I know it's not needed,
-- however in the scenario I ran into this there was much more code after the
-- exit. This is just the simplest way of reproducing it.
exit;
end
^

-- Now update the record to see the error:
update new_table set new_field = 6;

=>

Create the following database/trigger, and update the single record in the single table. You'll receive the message "Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
action cancelled by trigger (0) to preserve data integrity."

CREATE DATABASE 'C:\test.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 8192
DEFAULT CHARACTER SET NONE;

CREATE TABLE NEW_TABLE (
NEW_FIELD NUMERIC(9,2)
);

INSERT INTO NEW_TABLE (NEW_FIELD) VALUES (4);

SET TERM ^ ;

CREATE OR ALTER TRIGGER NEW_TRIGGER FOR NEW_TABLE
ACTIVE AFTER UPDATE POSITION 20
AS
-- Typically this variable would be used in the trigger. To make it simple, I've
-- removed all code possible to still get the error.
declare variable NewHours numeric(9,2) = 0;
BEGIN
-- Remove the coalesce and the update will work.
-- Remove the multiplication and the update will work.
-- Remove this unneeded variable and the update will work.
NewHours = coalesce(new.new_field * (0), 0);

-- Remove this exit command and the update will work. I know it's not needed,
-- however in the scenario I ran into this there was much more code after the
-- exit, and the exit was inside an IF clause. This is just the simplest way of reproducing it.
exit;
end
^

-- Now update the record to see the error:
update new_table set new_field = 6;

assignee: Dmitry Yemanov [ dimitr ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 RC2 [ 10372 ]

Link: This issue is related to QA428 [ QA428 ]

status: Resolved [ 5 ] => Closed [ 6 ]

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully