Tagged Questions

Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

2
votes
1answer
646 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 ...
2
votes
3answers
2k 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
2k 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
430 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 ...
5
votes
2answers
748 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
219 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
816 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
579 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
1k 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
885 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
217 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
168 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
828 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
205 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 ...
2
votes
2answers
919 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
89 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
6k 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 ...
4
votes
3answers
4k 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 ...
4
votes
4answers
21k 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
337 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 ...
5
votes
2answers
5k 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
305 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
2k 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
1k 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
499 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
1k 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
210 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
221 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.
3
votes
1answer
7k 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
3k 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 ...
5
votes
4answers
864 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
2answers
2k 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
2k 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 ...
8
votes
4answers
320 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
645 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 ...
2
votes
2answers
809 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
2k 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
1k 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
3answers
1k 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
1answer
6k 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 ...
7
votes
2answers
4k 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
2k 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 ...
5
votes
7answers
438 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
648 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
3k 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 ...
3
votes
3answers
418 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 ...
6
votes
2answers
2k 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
421 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 ...
1
vote
1answer
7k 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 ...
3
votes
2answers
3k 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 ...