Procedural code automatically executed in response to a database event.
1
vote
1answer
663 views
Keeping 2 tables in sync
I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, ...
2
votes
1answer
85 views
Is it possable to do a ALL SERVER DML Trigger?
My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they ...
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 ...
1
vote
3answers
148 views
A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete
THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line.
THe trigger thus far is:
CREATE OR ...
1
vote
1answer
607 views
bad bind variable in trigger
i'm trying to update the table whenever complaint has been registered
create or replace trigger complaint_advertise
BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
DECLARE BEGIN
UPDATE ...
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 ...
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
2answers
441 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
349 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
221 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
466 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
146 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
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
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 ...
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
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
625 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 ...
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 ...
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 ...
7
votes
3answers
4k 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 ...
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
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 ...
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 | ...
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 ...
4
votes
3answers
534 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 ...
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 ...
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
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 ...
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 ...
1
vote
1answer
763 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
585 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 ...
9
votes
3answers
995 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 ...
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 ...
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 ...
4
votes
1answer
773 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....) ?
...
5
votes
3answers
719 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?
...
