Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
26
votes
1answer
26k views

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. ...
22
votes
2answers
2k views

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 ...
22
votes
1answer
6k views

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 ...
21
votes
4answers
19k views

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....
21
votes
1answer
13k views

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 ...
18
votes
2answers
11k views

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 ...
17
votes
3answers
24k views

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 ...
17
votes
1answer
6k views

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 ...
16
votes
4answers
84k views

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 -- ...
16
votes
3answers
16k views

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?
16
votes
3answers
4k views

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 ...
15
votes
3answers
10k views

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 ...
13
votes
2answers
35k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as: CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...
13
votes
3answers
10k views

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....) ? ...
13
votes
5answers
41k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's SQL server. These databases are under development, so developers can design, refactor, do data modifications and so on. There are some databases that change ...
12
votes
3answers
3k views

How to change the firing order of Triggers?

Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
12
votes
2answers
2k views

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 ...
12
votes
1answer
19k views

How to use COLUMNS_UPDATED to check if any of certain columns are updated?

I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed. I can use UPDATE() function ...
12
votes
1answer
20k views

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 ...
12
votes
4answers
2k views

Is it poor practice to aggregate data from different tables into one?

Background I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different ...
12
votes
3answers
6k views

Dynamic SQL in MySQL stored routines

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
11
votes
2answers
76k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
11
votes
5answers
10k views

Atrocious performance joining INSERTED and DELETED tables in a trigger

I've got an UPDATE trigger on a table that watches for a specific column changing from one specific value to any other value. When this happens, it updates some related data in another table via a ...
11
votes
3answers
14k views

Passing info on who deleted record onto a Delete trigger

In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic. I can track Inserts/Updates by ...
11
votes
1answer
6k views

Why am I NOT getting a mutating table error in trigger?

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation: A mutating table is a table that is being modified by ...
11
votes
2answers
787 views

How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t .... Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
11
votes
1answer
7k views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
10
votes
1answer
1k views

Trigger firing despite no rows being affected

This is more of a general question but the motivation for this question was a problem I faced while using SQL Server. I have this trigger attached to an Insert event on a table that contains some ...
10
votes
1answer
2k views

How to check in a trigger if the entire row remains the same after an update?

Of course i could do it for every column comparing like this: if (old.column1 = new.column1 and old.column2 = new.column2...) But it would b hard coded and hard to maintain if I add another column ...
10
votes
1answer
1k views

What is the correct way to ensure unique entries in a temporal database design?

I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
10
votes
1answer
4k views

Synchronization using triggers

I have a requirement similar to previous discussions at: Writing a simple bank schema: How should I keep my balances in sync with their transaction history? Trigger in combination with transaction I ...
10
votes
1answer
1k views

Permissions in triggers when using cross database certificates

I use cross database certificates (as explained by Erland Sommarskog) to control access to a certain database in my environment ( SQL Server 2008 R2 ). I have stored procedures in database A that ...
10
votes
1answer
45k views

How to create a trigger that update the date field and time field upon modify of the row?

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
10
votes
2answers
11k views

Performance of a Trigger vs Stored Procedure in MySQL

A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
10
votes
2answers
3k views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
9
votes
3answers
1k views

How is a T-SQL Trigger that only fires on real changes made?

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it ...
9
votes
4answers
5k views

How can I know which stored procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
9
votes
1answer
105 views

Why does performing a delete on my partitioned view result in a Clustered Index Insert?

I have a partitioned view that has the below insert trigger (poor mans partition). When I perform a DELETE, I get the below query plan: delete from factproductprice where pricedate = '20170725' ...
9
votes
1answer
24k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it....
9
votes
1answer
620 views

How can I rewrite for SQL Server a trigger that writing for Oracle?

How can I rewrite for SQL Server a trigger that was originally written for Oracle ? Here is my Oracle trigger code: CREATE OR REPLACE TRIGGER P000KUL_TEST BEFORE INSERT ON P000KUL REFERENCING NEW AS ...
8
votes
3answers
977 views

Trigger to change Database collation on creation

I'm trying to create a Trigger, to alter collation of a database on its creation, but how can I catch the database name to use inside the trigger? USE master GO CREATE TRIGGER ...
8
votes
3answers
15k views

How to get last identity row Inserted when using instead of trigger

When I insert into tables using instead of triggers, @@Identity, IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?
8
votes
3answers
4k views

Wise to use trigger to update another table?

I have an Object table which is populated from an Integrated service (which I can change if needed) from another database. At certain points we need to manually add posts in another table ...
8
votes
3answers
10k views

Launching a python script from an insert trigger

We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to ...
8
votes
1answer
7k views

How to access NEW or OLD field given only the field's name?

I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
8
votes
3answers
5k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
8
votes
3answers
4k views

How to log data changes in Access at the table level?

I support an Access database for a non-profit organization. We have some issues with data mysteriously changing, and there been some, um, friendly debate over whether this is caused by users who are ...
8
votes
1answer
11k views

What is the difference between ON SCHEMA and ON DATABASE triggers?

We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference ...
8
votes
1answer
265 views

How can I resolve a database trigger's name with built-in functions?

I have a database trigger that I use to prevent me from creating certain procedures in user databases. It appears in sys.triggers, with an object_id, but I can't use the object_id function to find it....
8
votes
2answers
2k views

Is pg_trigger_depth() bad to use for preventing trigger cascading (recursion)?

Why is pg_trigger_depth() = 0 bad to use (for anything other than debugging) when preventing trigger cascading (recursion)? Can someone provide code to demonstrate why it is bad? I am guessing ...