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.

asked 04 Jun '12, 12:14

Ryan%20Erickson's gravatar image

Ryan Erickson
51126
accept rate: 0%

edited 04 Jun '12, 12:57

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

(04 Jun '12, 12:33) Breck Carter

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

(04 Jun '12, 12:35) Ryan Erickson
Replies hidden

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

(05 Jun '12, 12:03) Volker Barth

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?

permanent link

answered 04 Jun '12, 13:06

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

1

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

(04 Jun '12, 13:12) Ryan Erickson
Replies hidden

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

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

Correct! :)

(04 Jun '12, 13:34) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406
×88
×36
×22
×10

question asked: 04 Jun '12, 12:14

question was seen: 1,540 times

last updated: 05 Jun '12, 12:03