Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

A database trigger is procedural code that runs automatically in response to an event on occurring on the database. Triggers are commonly used for events on tables and views where they typically help maintain data integrity.

Table events that can fire triggers include:

  • Insert (Before, After) (Statement, Row)
  • Update (Before, After) (Statement, Row)
  • Delete (Before, After) (Statement, Row)

Some platforms include other events such as:

  • DDL Triggers (SQL Server, Oracle - Can be schema specific)
  • DB Level Triggers (Firebird, Oracle)
  • Compound Triggers (Oracle)
  • Instead Of Triggers (Oracle)
  • System Triggers such as Startup, Shutdown, and Server Error (Oracle)
  • Allowances for CREATE TABLE within Standard Events (PostgreSQL)
1528 questions
31
votes
1 answer

Trigger in combination with transaction

Suppose we have the following situation: We have a table (let's say Table_A), wich has a trigger on INSERT. The trigger job is to update some rows in table_B based on the inserted values in table_A. Now, everything is ok when we simple insert a row…
veljasije
  • 815
  • 2
  • 12
  • 20
28
votes
3 answers

Is it possible to modify an existing trigger definition in MySQL?

I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it. My assumption is that I need to drop and recreate it. What are the best practices in the…
Alex
  • 413
  • 2
  • 5
  • 7
26
votes
4 answers

How to limit maximum number of rows in a table to just 1

I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as…
Dib
  • 447
  • 1
  • 6
  • 13
24
votes
1 answer

Is there a good way to run a trigger for each record in a postgres table?

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the replicated tables, and store it in the processed…
Joe
  • 5,189
  • 1
  • 29
  • 39
24
votes
2 answers

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
24
votes
1 answer

What is the difference between an after update and a before update in PostgreSQL

What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was always executed before update. So I made the…
oriaj
  • 375
  • 1
  • 2
  • 7
23
votes
2 answers

Do triggers compile each time?

We are troubleshooting a server that has high CPU utilization. After finding that the queries weren't really causing it, we started looking into compilations. Performance Monitor is showing less than 50 Compilations/sec and less than 15…
Tara Kizer
  • 5,986
  • 1
  • 17
  • 28
22
votes
3 answers

SQL Server : How to disable trigger for an update only for your current session?

I am working on SQL Server 2008 R2. I have a table benefit which has a AFTER INSERT, UPDATE trigger named tiu_benefit. I want to write an UPDATE statement for this table to update 1 row but I dont want its trigger to fire. I know I can disable…
srh
  • 323
  • 1
  • 2
  • 5
22
votes
4 answers

How can I drop all triggers in a single database?

I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?
Mohamed Mahyoub
  • 363
  • 1
  • 2
  • 7
21
votes
2 answers

Is it possible to get execution call stack in a trigger?

I have 10 stored procedures and each of them does INSERTs into one tableX. Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ? Thank you.
garik
  • 6,782
  • 10
  • 44
  • 56
21
votes
1 answer

Should I add SET NOCOUNT ON to all my triggers?

It is fairly common knowledge that you should have SET NOCOUNT ON by default when creating new stored procedures. Microsoft has changed the default template to include this in 2012. I thought this should be the same for triggers, yet it is not…
DamagedGoods
  • 2,591
  • 6
  • 34
  • 48
21
votes
5 answers

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- AC - AllCount DECLARE AC INT DEFAULT 0; …
Mark D
  • 1,140
  • 4
  • 16
  • 28
20
votes
3 answers

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not using the stored procedures designed to update it.…
Elias
  • 311
  • 1
  • 2
  • 6
19
votes
2 answers

What is a “transition table" in Postgres?

The page describing what's new in Postgres 10 mentions “Transition Tables for Triggers”. Transition Tables for Triggers This feature makes AFTER STATEMENT triggers both useful and performant by exposing, as appropriate, the old and new rows to…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
18
votes
1 answer

What is the quickest way to purge data?

Scenario: We have two tables Tbl1 & Tbl2 on the Subscriber Server. The Tbl1 is being replicated from Publisher Server A and it has two triggers - insert and update. The triggers are inserting and updating the data into Tbl2. Now, we have to purge…
Dharmedra Keshari
  • 253
  • 1
  • 3
  • 6
1
2 3
…
99 100