Procedural code automatically executed in response to a database event.
17
votes
1answer
1k 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 ...
12
votes
3answers
818 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 ...
9
votes
3answers
1k 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 ...
7
votes
3answers
2k 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 ...
7
votes
1answer
630 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 ...
7
votes
3answers
5k views
How to detect any changes to a database (DDL and DML)
There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
7
votes
1answer
318 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 ...
6
votes
2answers
2k 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 ...
6
votes
3answers
853 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 ...
6
votes
2answers
2k 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 ...
5
votes
3answers
725 views
Alternatives to an INSERT Trigger
Looking for an example code in Oracle to do the same. i.e.
Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?
...
5
votes
1answer
313 views
If an 'after' DDL trigger causes an error, is the DDL rolled back?
It has been suggested that DDL is logically performed something like this:
begin
COMMIT;
perform any appropriate pre-DDL trigger code;
do the ddl;
perform any appropriate post-DDL ...
5
votes
1answer
529 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 ...
5
votes
1answer
2k 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 ...
5
votes
1answer
142 views
In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement?
Given a Table with a column col1 like this:
create table MyTest (
col1 int NULL,
col2 decimal(7, 2) NULL
);
insert into MyTest values ( 1, 1.1);
Is there a way for a trigger for update to ...
5
votes
2answers
284 views
considerations when using triggers on a replicated (target) database
Our SQL Sever 2008 application database is replicated from Server A to Server B (push replication). We use the copy, let's call it database_b, on Server B to create reports and run other queries so ...
5
votes
1answer
160 views
Triggers and the Transaction
We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic.
The logic is ...
4
votes
7answers
321 views
Policies RE database triggers in well-designed applications?
I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones.
I'm ...
4
votes
3answers
536 views
Making a column immutable in MySQL
I want to make a column in a relation unmodifyable for consistency reasons.
The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that ...
4
votes
3answers
943 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 ...
4
votes
1answer
875 views
Why doesn't INSERT AFTER Trigger add all results?
I'm working on the exercises for db-class.org where we have to write a trigger that makes new students named 'Friendly' automatically like everyone else in their grade. That is, after the trigger ...
4
votes
2answers
638 views
Is there a way to ensure that a SQL Server trigger will be executed?
My supervisor is hesitant to use a trigger in a process because if there is a network interruption at just the right time, the process would not complete. Does SQL Server include the trigger in the ...
4
votes
2answers
377 views
Implementing a Circular Buffer (Sliding window) in MySQL
I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the ...
4
votes
2answers
152 views
Can I be notified of a rollback in my DDL Trigger?
I'd like DDL changes in SQL Server to trigger an external process (outside of SQL). The problem is, is there a way for me to be notified via my trigger that a rollback occurred?
For instance, with ...
4
votes
2answers
2k views
Database logon trigger
To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors.
Create or replace trigger you_may_not_login
after logon on database
...
4
votes
1answer
898 views
Can a trigger access the query string?
I'm thinking about using triggers as a logging mechanism in MySQL (v. 5.1) and therefore I'd like my trigger to get the query string in order to store it in another tabe.
I couldn't find anything ...
4
votes
1answer
786 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....) ?
...
4
votes
1answer
160 views
How can I know which store 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 ...
4
votes
1answer
136 views
Prevent users deleting more than a certain number of rows in MySQL
The problem:
I have a table with rows, nothing special about it
When a database user removes a row, it should increment a number
A database user is only allowed to remove 3 rows max in the table
A ...
3
votes
1answer
185 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.
...
3
votes
2answers
589 views
Trigger shouldn't block insert
I have a trigger on insert but if the trigger fails the insert fails too.
is there a way to let the insert proceed even if the trigger fails?
EDIT: I use a trigger to send a email when a new record ...
3
votes
3answers
294 views
How to determinate in a trigger direct inserts and inserts via stored procedure
TableX can be modified in two ways:
client does "direct" inserts
client uses stored procedure to inserts records
How to determinate the way of client's call (direct or stored proc) in a trigger of ...
3
votes
1answer
439 views
Why I don't need to COMMIT in database trigger?
We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database ...
3
votes
1answer
142 views
Error when UPDATE all rows
When I update a single row, it works fine. But when I update all rows with:
UPDATE cad_bilhetes
SET ligacao_acobrar = 'False'
I get the following error:
Msg 512, Level 16, State 1, Procedure ...
3
votes
1answer
127 views
Check what event called the trigger on PostgreSQL?
I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE.
Oracle triggers can check this in a simple 'IF' statement:
IF INSERTING, IF UPDATING or even IF DELETING
Is there a ...
3
votes
1answer
116 views
Restrict record to be update or deleted according to dates inside row for SQL Server 2005
I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher.
I now know that if someone updates, ...
3
votes
2answers
1k views
MySQL trigger: Compare value in another table
I have the following trigger (generated using PHP via mysqli)...
DROP TRIGGER IF EXISTS post_queue_insert;
CREATE TRIGGER post_queue_insert AFTER INSERT ON posts
FOR EACH ROW BEGIN
IF ...
3
votes
2answers
1k views
Trigger in MySQL
Please help me for few question in SQL..
I want to create the trigger which prevents insertion in the reservation table of flights (numflights), which does not exist.
Tables
...
3
votes
1answer
225 views
How to qualify variable inside trigger body (PL/SQL )?
I can explicitly qualify variable in the body of stored procedure or function :
create or replace
PROCEDURE ptest AS
int_val INT;
BEGIN
ptest.int_val:=0;
END;
/
How to do the same inside trigger ...
3
votes
1answer
363 views
PostgreSQL pl/perl trigger, differentiate null vs empty
I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the ...
3
votes
2answers
463 views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: ...
3
votes
1answer
6k 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, ...
3
votes
1answer
130 views
For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?
Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns?
Like if I have this table:
CREATE TABLE [dbo].[tbl_shipping_boxes]
(
...
3
votes
2answers
354 views
Transaction independent writes inside a trigger
I am using sql express 2008 r2 express edition.
Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire?
I have triggers ...
3
votes
1answer
166 views
Accessing ORA_ROWSCN in the triggers body
Is it possible to access ORA_ROWSCN pseudo-column in the body of BEFORE triggers? I'm thinking about storing this value in the table itself to emulate SQLServer rowversion behaviour.
2
votes
4answers
312 views
A database trigger is an alternative for?
I had the following quiz question and could not answer it myself because of a little confusion.
Please answer this and tell me why?
A database trigger is an alternative for?
A. Stored ...
2
votes
2answers
628 views
Oracle - abort within a “before insert” trigger without throwing an exception
I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
2
votes
2answers
83 views
Is there a way that I can export a list of all table triggers on a server?
I have two SQL Server instances that I manage. One is a SQL Server 2000 instance, and the other is 2005.
Some where, on these servers, I recall setting up a couple table triggers that were executed ...
2
votes
4answers
6k 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
-- ...
2
votes
2answers
303 views
Securely Send Database Mail via an Insert Trigger
I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet ...
