Description:
A table has a trigger
the trigger uses a view
After DDL on the view, the trigger is still cached and executed with the old view definition.
See related Bug #33000 (Triggers do not detect changes in meta-data.)
How to repeat:
--disable_warnings
drop table if exists t7_view;
drop table if exists t7_audit;
drop view if exists audit_view;
--enable_warnings
create table t7_view(a int);
create table t7_audit(old_a int, new_a int, reason varchar(50));
create view audit_view as select "view v1" as reason from dual;
create trigger t7_view_bi before insert on t7_view for each row
insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));
insert into t7_view(a) value (301);
insert into t7_view(a) value (302);
drop view audit_view;
create view audit_view as select "view v2" as reason from dual;
insert into t7_view(a) value (303);
insert into t7_view(a) value (304);
# Expecting:
# - NULL, 301, view v1
# - NULL, 302, view v1
# - NULL, 303, view v2
# - NULL, 304, view v2
# Actual:
# - NULL, 301, view v1
# - NULL, 302, view v1
# - NULL, 303, view v1 <-- bug
# - NULL, 304, view v1 <-- bug
select * from t7_audit order by new_a;
FLUSH TABLES;
insert into t7_view(a) value (305);
insert into t7_view(a) value (306);
# Now the trigger uses view v2,
# because of the flush
select * from t7_audit order by new_a;
drop table t7_view;
drop table t7_audit;
drop view audit_view;
Suggested fix:
Implement object dependency tracking and validation.
