The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

If you neglect situations like system crashes, or power failure, is it possible in the system, that under any circumstances an after insert trigger is not fired? If so, what circumstances would lead to such an effect?

(I know that LOAD TABLE, TRUNCATE, and WRITETEXT will not fire triggers)

asked 24 Mar '11, 06:56

Martin's gravatar image

accept rate: 14%

So you say, the row is inserted successfully but the trigger hasn't fired at all?

(24 Mar '11, 08:39) Volker Barth
Replies hidden

correct, but it is just an assumption to get a better understanding

(24 Mar '11, 09:12) Martin

Well, I'm not sure whether you ask about a trigger that should fire, and does not in a particular situation, or ask more generally.

In addition to Thomas's answer, there are a few more situations I'm aware of:

  1. You can generally prevent all triggers from firing (including system triggers for RI action) by setting option fire_triggers to OFF or by alternatively starting the database engine with the -gf switch - the latter preventing triggers in all databases run on that engine.
  2. For AFTER UPDATE triggers: "If a column-list is specified for an AFTER UPDATE trigger, the trigger is fired only if the value of any of the columns in column-list is changed by the UPDATE statement."
  3. For INSTEAD OF triggers: "If there are competing statement-level and row-level INSTEAD OF triggers, the row-level triggers do not fire."
  4. Software bugs (cf. this solved one).

EDIT: Sorry, I should have read your question better - so, w.r.t. AFTER INSERT triggers, only point one could be a reason.

As to the reliability of triggers, I do rely on them. To quote from the CREATE TRIGGER page:

If an error occurs while a trigger is executing, the operation that fired the trigger fails. INSERT, UPDATE, and DELETE are atomic operations. When they fail, all effects of the statement (including the effects of triggers and any procedures called by triggers) revert to their preoperative state. See Atomic compound statements.

As such, as far as triggers are not explicitely prevented (s.a.), they should work in an atomic way.

That being said, I would only use triggers for tasks that cannot be handled by other features like

  • unique and foreign keys
  • datatypes, domains and defaults
  • check constraints
  • computed columns
  • special values like DEFAULT TIMESTAMP, LAST USER and the like.

As Breck wrote in his well-knwon book: '"Avoid writing triggers." - " - if possible"*, I would add:)

permanent link

answered 24 Mar '11, 08:17

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 24 Mar '11, 09:34

I'm am asking in general without any real observation. I would like to better understand how much a system can rely on triggers.

(24 Mar '11, 09:09) Martin

Not a answer to your question but I know that an AFTER UPDATE Trigger is not fired when content of the fields in the row is not changed.

A statement like

update <TABLE> 
    set <FIELD> = <FIELD>

will not force the execution of an after update trigger.


permanent link

answered 24 Mar '11, 07:31

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
accept rate: 16%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 24 Mar '11, 06:56

question was seen: 1,581 times

last updated: 24 Mar '11, 09:34