Procedural code automatically executed in response to a database event.
1
vote
1answer
345 views
when will Innodb support activation of triggers on cascade foreign key? [closed]
The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
Currently, cascaded foreign key actions do not activate triggers.
Any Idea when will this be implemented.
Is ...
1
vote
1answer
612 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
1answer
264 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
2answers
170 views
SQL Server 2012, restore database
I am using MS SQL Management Studio 2012 to restore the database from a .bak file. (That file is from the backup of the database on server)
In my new database, the triggers are missing. Why did I ...
1
vote
1answer
65 views
DB2 9.7 - Find rows inserted by a specific trigger
We are auditing a table TRANSACTIONS that inserts a row in HISTORY_TRANSACTIONS when specific columns are changed. These inserts happen through a number of triggers defined on TRANSACTIONS table. Is ...
1
vote
1answer
181 views
How to write signal function in MySQL that can be called from Triggers and Stored Functions?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
The Table Description:
mysql> DESC Employee;
...
1
vote
2answers
356 views
How to insert into junction table using triggers
Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.
Artist and Song are base tables and SongArtist is the ...
1
vote
1answer
2k views
Declaring and using variables in triggers
I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem.
DELIMITER $$
USE `pucko`$$
CREATE TRIGGER `after_delete_Alien`
AFTER ...
1
vote
1answer
49 views
Deadline rule date input after now
Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date.
Currently I'm attempting this with a rule.
CREATE OR REPLACE RULE "DeadlineInput" AS
ON INSERT ...
1
vote
1answer
463 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
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 ...
1
vote
1answer
33 views
Trigger not firing when deleting a record from stored procedure MySql
Bare with me, as I'm fairly new to stored procedures / triggers.
I have the following Stored Procedure:
DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
1
vote
1answer
54 views
Is it possible for a trigger to update a row in a table using a value that was deleted?
I have a trigger which is updating a column in a different table after the row is inserted:
mysql>delimiter $$
mysql> create trigger status
after insert on table2
FOR EACH ROW
BEGIN ...
1
vote
1answer
63 views
What would cause extra records to be inserted during a INSERT trigger
The following trigger that joins Inserted table on itself creates multiple unwanted records:
CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
IF EXISTS(SELECT 1 FROM ...
1
vote
1answer
23 views
Determine if existing Trigger acts on field DB2
I am eventually going to be writing a trigger on a field named "user2". Currently most of the values in this field are blank, but there are some seemingly random rows with values of '00' for the ...
1
vote
1answer
232 views
How can I ensure that in a insert trigger a column gets filled before other column?
The bigger picture:
tableX represents pipelines and tableY streets. A pipeline crosses/goes along many streets, and I want to know which street is crossed the most. First, I am segmentizing each ...
1
vote
1answer
196 views
Inherit audit columns and triggers
Background
I have a database with dozens of tables, some of which should have audit columns.
Problem
I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
1
vote
2answers
49 views
PostgreSQL custom domain with trigger
Is it possible to create custom domain / data type and associate some trigger with it, for example hash password?
I will have for example password domain and if I set some value to this column, it ...
1
vote
1answer
139 views
processlist showing '%' as host from trigger
I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:
CREATE TRIGGER access_insert_trg BEFORE INSERT ...
1
vote
1answer
359 views
How do triggers affect last_insert_id()?
I am unable to test this right now, however I'm curious about the answer.
If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B? ...
1
vote
1answer
666 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, ...
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
2answers
448 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
666 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
768 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 ...
1
vote
2answers
428 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 ...
1
vote
1answer
29 views
Postgresql - how to drop a trigger [closed]
A while ago I've created a trigger in Postgresql, update_user, which sets the update_date to now() for table users. Now I want to drop this trigger.
The following queries don't work:
drop trigger ...
1
vote
2answers
29 views
Federated tables and triggers
This is the scenario.
Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2).
I have a table (T2) on DB2 that needs to "fectch" rows from another table (T1) on ...
1
vote
0answers
30 views
Why Oracle Database trigger can became DISABLED
We have an application, using ddl database trigger on oracle database.
Trigger depends on package and several tables, package depends on user defined function.
It works on different customer sites ...
1
vote
0answers
205 views
Trouble with triggers and insert .. on duplicate key update
I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
1
vote
0answers
88 views
TSQL Trigger Incorrect Syntax
Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G:
INSERT INTO aTable
(
tTypeId,
opId,
inserted.Id
)
SELECT
3,
0,
inserted.Id
...
1
vote
1answer
214 views
MySQL: Try to Port a Trigger from SQL Server
Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause:
CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg]
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg]
ON [dbo].[Test_PTA_Table]
...
1
vote
1answer
140 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 ...
0
votes
1answer
113 views
How triggers are called when we have two triggers?
In a table I have two triggers. When it is called for the first time the first trigger does not fires while the second trigger's got fired. On the next call both first and second trigger action take ...
0
votes
2answers
65 views
PLSQL: BEFORE INSERT Trigger
I am attempting to write my first trigger statement. I am trying to create a trigger that performs a SELECT to get my MAX_TOW_WEIGHT for a LOCO_CLASS, then compare that weight to the TRAIN_WEIGHT. If ...
0
votes
1answer
122 views
Derived value not calculating as expected using combination of FK and Triggers (MySQL)
I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints.
There are 3 tables involved in this arrangement
responses
------------------------
...
0
votes
1answer
94 views
Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'
I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a ...
0
votes
1answer
181 views
one trigger for multiple tables
i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 ...
0
votes
1answer
65 views
Synchronise data from one table to another & vice-versa using triggers
We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases ...
0
votes
1answer
39 views
Postgres - Schema information within Trigger?
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).
Reviewing patterns for the following problem: I ...
0
votes
1answer
42 views
How can I modify this trigger with loops and concat?
I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name ...
0
votes
1answer
31 views
Is there a workaround for lack of support of cascade triggers with MySQL?
As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
0
votes
2answers
79 views
Should I disable triggers, timestamps in a slave in mysql?
We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
0
votes
1answer
59 views
What happens to a Trigger if a table is dropped and rename and recreated with same name
What happens to a Trigger if a table is dropped and rename and recreated with same name?
For e.g:
We have a Trigger X which is define for Table A.
We have a Trigger Y which is define for Table B.
...
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 ...
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 ...
0
votes
1answer
37 views
MySQL Replication and Triggers
I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret.
The setup:
There are two tables A and B.
When data is written or updated in the table A, then a ...
0
votes
1answer
47 views
Disabled trigger still fired
I've just run the following t-sql to disable a trigger (with a deploy script wrapper around osql):
disable trigger dbo.trExampleTableInsert ON dbo.ExampleTable
In SSMS I could see the trigger was ...
0
votes
0answers
25 views
Fast afterUpdate cross-database trigger blocks insert
I'm trying to fix a timeout issue in an existing application.
There is a trigger on after_update that cascades a value from the update from master.Client to internal.Client and external.Client
The ...
0
votes
0answers
56 views
Resolving a “Multiple triggers with the same action” error [closed]
What's wrong with my trigger? I'm getting this error:
1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
whats causing this ...
