ON DELETE CASCADE not functioning

[Former Member]

I am running sa 12.0.1.3726 and having issues with my system triggers when executing the following script

CREATE TABLE test1 (id INTEGER PRIMARY KEY);
CREATE TABLE test2 (id2 INTEGER PRIMARY KEY, id INTEGER, FOREIGN KEY id(id) REFERENCES test1(id) ON UPDATE CASCADE ON DELETE CASCADE);

insert into test1(id)values(1);
insert into test2(id2,id)values(1,1);
delete from test1;

I get Primary key for row in table 'test1' is referenced by foreign key 'id' in table 'test2' when it should be handled by the ON DELETE rule.

[Breck_Carter]

It works OK for me in 12.0.1.3298, so it must be a new feature bug.

[Former Member]

It also worked for my co-worker on 12.0.1.3718 so it must be very new.

[VolkerBarth]

FWIW, it's not very new, it's reaaaaal old - even v5.5 contains that feature:

FIRE_TRIGGERS

When set to ON (the default), triggers are fired. When set to OFF, no triggers are fired, including referential integrity triggers (such as cascading updates and deletes). Only a user with DBA authority can set this option. The option is overridden by the -gf command-line option, which turns off all trigger firing regardless of the FIRE_TRIGGERS setting.

Just for the record:)

[Former Member · Accepted solution]

I have no issues with either 12.0.1.3456 or 12.0.1.3741.

Is this a simplified repro of a larger problem?

Is is possible that you have the fire_triggers option set, or used the -gf server option?

[Former Member · Accepted solution]

It appears one of my earlier scripts turned off fire_triggers(then errored out and never turned it back on). Turned it back on and it functioned as expected

[Breck_Carter · Accepted solution]

The answer is "fire_triggers", what is the question?

"How do I mess with people's minds?"

Correct! 🙂