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)
1374 questions
28
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
  • 765
  • 2
  • 10
  • 19
25
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
  • 437
  • 1
  • 5
  • 11
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
  • 4,909
  • 1
  • 26
  • 37
23
votes
2 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
  • 363
  • 2
  • 3
  • 7
22
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,726
  • 12
  • 28
21
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
  • 10,129
  • 27
  • 75
  • 94
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,561
  • 6
  • 30
  • 48
21
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
  • 313
  • 1
  • 2
  • 5
19
votes
3 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,492
  • 10
  • 41
  • 56
19
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
  • 301
  • 1
  • 2
  • 6
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
18
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
  • 323
  • 1
  • 2
  • 7
18
votes
4 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,061
  • 4
  • 15
  • 26
17
votes
3 answers

Slow deletion of records when a trigger is enabled

Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve. http://support.microsoft.com/kb/2606883 Ok so I have an issue that I wanted to throw out to StackOverflow to…
tsells
  • 349
  • 3
  • 10
16
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
  • 8,776
  • 14
  • 46
  • 78
1
2 3
…
91 92