PK constraint should be prevented from dropping if there is procedure/trigger
which uses it in UPDATE OR INSERT statement (otherwise such SP will work
incorrect)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-5212
URL: http://tracker.firebirdsql.org/browse/CORE-5212
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 3.0.0, 4.0 Initial, 2.5.5
Reporter: Pavel Zotov
Consider script:
===
set echo on;
create or alter procedure sp_test as begin end;
recreate table test(
u int not null, v int not null, w int not null
);
alter table test add constraint test_pk primary key( u );
commit;
set term ^;
create or alter procedure sp_test(a_u int, a_v int, a_w int) as
begin
update or insert into test( u, v, w ) values ( :a_u, :a_v, :a_w );
end ^
set term ;^
commit;
execute procedure sp_test( 1, 10, 20);
execute procedure sp_test( 2, 20, 40);
execute procedure sp_test( 1, 11, 22);
select * from test;
delete from test;
commit;
alter table test drop constraint test_pk;
commit;
-- //////////////////////////////////////////////////////
alter table test add constraint test_pk primary key( v );
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
commit;
show table test;
/*
set term ^;
create or alter procedure sp_test(a_u int, a_v int, a_w int) as
begin
update or insert into test( u, v, w ) values ( :a_u, :a_v, :a_w );
end ^
set term ;^
commit;
*/
execute procedure sp_test( 1, 10, 20);
execute procedure sp_test( 2, 20, 40);
execute procedure sp_test( 1, 11, 22);
select * from test;
===
If you will NOT make recreation of SP_TEST after dropping PK (and assign it to
another column), this SP will work wrong: it seems like OLD PK definition is
stored somewhere.
With __NEW__ primary key (column'V') last three statements will produce content
of table TEST like with __OLD__ PK = 'U':
===
execute procedure sp_test( 1, 10, 20);
execute procedure sp_test( 2, 20, 40);
execute procedure sp_test( 1, 11, 22);
select * from test;
U V W
============ ============ ============
1 11 22
2 20 40
===
So, one need to empty bodies of all such procedures / triggers before this PK
will be dropped, and later to fill them again + recompile.
It will be great if this job could be done in the single transaction by engine
itself.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
