Question Re: ON DELETE CASCADE not functioning in Technology Q&A

ON DELETE CASCADE not functioning

Former Member — Mon, 04 Jun 2012 12:14:22 GMT

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.

Re: ON DELETE CASCADE not functioning

Breck_Carter — Mon, 04 Jun 2012 12:33:49 GMT

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

Re: ON DELETE CASCADE not functioning

Former Member — Mon, 04 Jun 2012 12:35:53 GMT

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

Re: ON DELETE CASCADE not functioning

Former Member — Mon, 04 Jun 2012 13:06:21 GMT

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?

Re: ON DELETE CASCADE not functioning

Former Member — Mon, 04 Jun 2012 13:12:56 GMT

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

Re: ON DELETE CASCADE not functioning

Breck_Carter — Mon, 04 Jun 2012 13:34:48 GMT

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

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

Correct! 🙂

Re: ON DELETE CASCADE not functioning

VolkerBarth — Tue, 05 Jun 2012 12:03:25 GMT

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:)