Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
1answer
2k 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
8k views

find and insert row to another table using mysql trigger

I have the following three tables in mysql database named My_Company mysql> desc employee; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | ...
1
vote
1answer
189 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 ...
0
votes
1answer
2k 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
2answers
1k views

mysql trigger on update with insert statement

I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created. DELIMITER ;; CREATE ...
0
votes
1answer
584 views

Passing argument in trigger dynamically

CREATE TRIGGER audit_proc_tr AFTER INSERT OR UPDATE OR DELETE ON "log".hi FOR EACH ROW EXECUTE PROCEDURE "log".audit_proc(argument); CREATE OR REPLACE FUNCTION ...
0
votes
2answers
769 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 ...
1
vote
2answers
875 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 ...
2
votes
1answer
3k 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 ...
3
votes
2answers
1k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
2
votes
1answer
1k views

MariaDB, Triggers and last_insert_id

I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information). CREATE TABLE `sample` ( `sample_id` int unsigned NOT NULL ...
2
votes
2answers
1k 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 ...
0
votes
0answers
394 views

Error in creating trigger in phpMyAdmin

I have 4 tables: event: event_id(p.k) | uid | circle_id(f.k) activity: uid | performed_activity_id(f.k->event_id) | activity_type_id follow: follower_id | circle_id(f.k) notification: sender_id ...
2
votes
4answers
1k 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 ...
0
votes
1answer
273 views

What is the error in this TRIGGER?

The MySQL version I'm using is 5.1.46. The query is CREATE DEFINER = CURRENT_USER TRIGGER `movimentacao_before_del_tr` BEFORE DELETE ON `movimentacao` FOR EACH ROW IF (OLD.stMov IN ("E", "C", ...
12
votes
1answer
7k 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. ...
1
vote
1answer
148 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 ...
2
votes
1answer
238 views

INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

I am trying to write my first trigger. It is an insert trigger as follows: <b>Fatal error</b>: postgres7 error: [-1: ERROR: INSERT has more target columns than expressions at character ...
6
votes
1answer
902 views

Getting Identity values to use as FK in an INSTEAD OF trigger

I have a series of updateable views we are exposing to end users as the interface for a back end process. One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and ...
0
votes
0answers
2k views

MS Access trigger

I got a trigger to update a table in SQL. My actual need is in MS Access but I could not make it. So please help me to write the same trigger in MS Access. Query is as follows: Create trigger ...
3
votes
1answer
740 views

Which is more efficient, trigger or PHP code?

My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking ...
0
votes
1answer
3k 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 ...
-1
votes
1answer
428 views

Is it possible to create a trigger on a table synonym in SQL Server? [closed]

The title says (asks) it all. Is it possible to create a trigger on a table synonym in SQL Server (2005 or higher)?
2
votes
2answers
8k views

how to add trigger in mysql workbench, without UDF, using EER design

I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema: ...
1
vote
2answers
4k views

Multiple triggers vs a single trigger [closed]

Scenario: Each time data is inserted/updated/deleted into/in/from a table, multiple unrelated items of business logic need to be executed. Question Given that the solution is to use database ...
2
votes
2answers
1k 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 ...
1
vote
1answer
762 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 ...
2
votes
1answer
3k views

Msg 7391, Distributed Transactions (DTC) on SQL Server

This may be DBA related or developer related, I honestly don't know. I have two Database servers: A: SQL Server 2005 (Trigger that uses DBMail on Server B) B: SQL Server 2008 R2 (DBMail configured) ...
1
vote
2answers
2k 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 ...
2
votes
2answers
2k views

Comparing dates in a BEFORE INSERT trigger

I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use. Something like: CREATE TRIGGER chk_dates BEFORE INSERT ON ...
2
votes
1answer
902 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 ...
2
votes
1answer
401 views

trigger in MySQL to disallow multiple row deletions at a time on a table

How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query? delimiter \\ create trigger not_more_than_2_rows before delete on employee ...
6
votes
2answers
2k 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 ...
2
votes
2answers
1k views

Is it possible to monitor role and privilege grant/revoke using triggers?

Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.
2
votes
1answer
3k views

What is the difference between ON SCHEMA and ON DATABASE triggers?

We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference ...
5
votes
1answer
7k 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 ...
9
votes
1answer
2k 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 ...
1
vote
1answer
1k 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; ...
0
votes
1answer
134 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 ...
1
vote
1answer
75 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
649 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 ...
3
votes
1answer
334 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, ...
2
votes
2answers
1k 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 ...
3
votes
2answers
5k 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: ...
4
votes
2answers
3k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
1
vote
0answers
113 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 ...
2
votes
2answers
1k views

Postgresql after 'for each row' triggers don't actually execute after each row?

If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted ...
2
votes
1answer
286 views

MySQL generic trigger to save identification data for later accessing of the changed row

I am pretty inexperienced with this. I need a generic trigger, able to create and save in a fixed table some sort of identification data for a changed row from generic (any) table. The ...
1
vote
1answer
1k views

Performance of SQL Server Triggers

I'm coming from an Oracle/PostgreSQL background and have a hard time coping with the limits of SQL Server's trigger implementation. I want to update two different datetime columns if the value of a ...
5
votes
1answer
968 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 ...