Tagged Questions

Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
0answers
682 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
1answer
2k 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 ...
0
votes
1answer
230 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 ...
1
vote
1answer
462 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 ...
1
vote
1answer
475 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 ...
2
votes
2answers
5k views

Execution of a Java program by a trigger.

I want to make an update trigger execute a Java program. I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a Java program. ...
0
votes
1answer
1k 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
4k 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
122 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
1k 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 ...
-1
votes
1answer
379 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
330 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
665 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
2k 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 ...
2
votes
2answers
639 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
815 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
288 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) noiticication: ...
2
votes
4answers
886 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
218 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", ...
10
votes
1answer
4k 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
141 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
216 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
625 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
1k 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
528 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
2k 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
329 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
6k 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
3k 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
940 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
644 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
2k 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
1k 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
1k 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 ...
1
vote
1answer
664 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
339 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 ...
5
votes
2answers
1k 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
1answer
948 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
2k 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
5k 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
1k 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
754 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
132 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
66 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
491 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
271 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, ...
1
vote
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
4k 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: ...