Procedural code automatically executed in response to a database event.
2
votes
1answer
331 views
How to speed up audit trigger?
The task: write a trigger to log all updates within tbl1 table for future audit users' actions.
The tbl1 table:
id INT(11)
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some ...
1
vote
3answers
1k views
Is it normal to use many Triggers?
I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
1
vote
2answers
440 views
Adding Row Version to SQL Server table
I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
1
vote
1answer
190 views
Error with Oracle trigger, invalid identifier
I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.
DML statement
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
NVL((SELECT ...
3
votes
2answers
348 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 ...
5
votes
1answer
159 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 ...
3
votes
1answer
220 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 ...
1
vote
1answer
262 views
Triggers not being run on a partitioned table
I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
1
vote
1answer
509 views
Soft delete: Copy row data to archive table when deleting
How can I move data from my current table to an archive table when a delete is called on the table?
My current table and archive table needs to be in sync in their schema too.
Can someone help ...
2
votes
1answer
462 views
MySQL using trigger instead of check
Is there any way that I could use trigger instead of check? like I have
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER test BEFORE UPDATE ON apply
FOR EACH ROW BEGIN ...
4
votes
2answers
151 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 ...
2
votes
2answers
143 views
Validation of availability for a new order
I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
1
vote
1answer
461 views
SQL Server sp_send_dbmail Query Error
EXEC msdb.dbo.sp_send_dbmail
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com',
@query = 'SELECT * FROM INSERTED',
@attach_query_result_as_file = 1,
@subject = 'Auto-Generated Deleted ...
1
vote
1answer
145 views
Randomly generated password for mysql admin over time?
I have this idea of making my database's Admin account secured by creating the following:
Trigger is activated every hour in mysql this trigger activates a stored procedure
Stored procedure ...
1
vote
2answers
368 views
How to specify trigger execution order under PostgreSQL?
I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table.
CREATE TABLE twitter_interactions(...);
CREATE OR REPLACE ...
0
votes
1answer
46 views
Checking for ApplicationName in a transaction through a Before Trigger
I would like to be able to build a trigger that checks the ApplicationName coming across on a transaction, and if the ApplicationName is of a certain value, then I want to throw an error, saying the ...
2
votes
2answers
2k views
In a trigger, how to check if no fields have changed?
If we receive an update statement that does not check if the value has changed in the where clause, what are the different ways to ignore that update inside a trigger?
I know we can do a comparison ...
2
votes
3answers
1k views
Disable trigger for just one table
Is it possible to disable a trigger momentarily but for just one table.
For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
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
1answer
269 views
MySQL Trigger problem with the email address (cause contains the @ sign)
I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger:
CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
...
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
...
2
votes
1answer
192 views
Has anyone ran into this replication breaking bug before?
I've been having this issue where replication was breaking on colliding primary keys. The annoying thing was these were auto increment generated.
I think my issues been tracked down to ...
2
votes
1answer
1k views
Does MS Access have anything equivalent to triggers?
I'm developing some code that will eventually talk to an Oracle 10g database. While doing this, I am also redesigning the relevant database tables. I'm not a database pro, and I don't have sufficient ...
4
votes
2answers
623 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 ...
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 ...
1
vote
1answer
660 views
How to set up triggers in MySQL for copying a field
I have an in-house application that I've put together, and I'm VERY new to SQL in general.
I have a table that contains most of our data. Let's pretend it looks like this.
Matrix
|Name | ...
1
vote
1answer
139 views
Create a trigger that compares a field, with a field in other table
I have 3 databases, (One principal, one Mirror and a witness) and I'm trying to make this system the most fault tolerant as possible.
So I was thinking in doing some extra codding to protect me ...
3
votes
1answer
165 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
1answer
3k views
Why TRY CATCH does not suppress exception in trigger
I have a trigger on a table (source) that data should be copied to the other one (target) in other database. I am trying to implement custom synchronization process for data: I want that target ...
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 ...
4
votes
3answers
529 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
1answer
895 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
871 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
1answer
158 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 ...
3
votes
1answer
359 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 ...
1
vote
2answers
412 views
Is it possible for me to be notified if an 'after insert or update trigger' stops working?
Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again.
I do not yet know why it stopped working. Is there any way to deal with ...
12
votes
3answers
802 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 ...
1
vote
1answer
762 views
ADMINISTER DATABASE TRIGGER privilege
What does ADMINISTER DATABASE TRIGGER privilege actually mean?
I have the following case:
I have 2 databases: DB1 and DB2
On DB1 I have a trigger which arises after logon on database and checks if ...
3
votes
2answers
584 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 ...
1
vote
1answer
2k 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 ...
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 ...
2
votes
3answers
848 views
What are the best practices for triggers to maintain a revision number on records?
I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number ...
4
votes
7answers
320 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 ...
1
vote
2answers
424 views
Are there any MySQL Triggers or Events that my program can wait for?
Say I want a live feed of emails on a web based email system. Emails are inserted into MySQL tables. Let's say I want my program to know immediately when the table is updated so it can check for new ...
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 ...
0
votes
0answers
385 views
AFTER INSERT Trigger [closed]
I have a table called Customer_Career and there are ID,CusID,SponsorID,CareerID,PeriodID columns.
Secondly,I have a table called Customer_Career_Period and there are ...
3
votes
3answers
293 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 ...
4
votes
1answer
772 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....) ?
...
2
votes
1answer
353 views
SQL Server Logon Trigger causing problems with the Distributed Transaction Coordinator
I've created the following simple logon trigger on my SQL Server 2005:
CREATE TRIGGER LOGON_RESTRICTION ON ALL SERVER
FOR LOGON
AS
BEGIN
PRINT 'Hello World'
END
GO
Which seems to be working fine ...
0
votes
1answer
3k views
store result set in temporary table, variable or separate variables within a trigger
I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I ...
