Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
0answers
14 views

Use trigger to synchronize columns with fields in json column on insert or update

I'm a bit of a database/postgres beginner,so bear with me. If I have a table, something like this. CREATE TABLE testy ( id INTEGER REFERENCES other_table, name varchar(128) PRIMARY KEY, ...
2
votes
2answers
43 views

MySQL trigger after insert with insert and delete instructions does not work properly

I have the following trigger created in phpMyAdmin: Table: tb_agenda Time: AFTER Event: INSERT BEGIN INSERT INTO tb_realizacao (dt_agenda, titulo, ...
0
votes
0answers
21 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
2
votes
1answer
24 views

MySQL stored procedure updates twice

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
1
vote
0answers
12 views

Whitespace Trimming on Before Trigger in PL/SQL [on hold]

I'm looking to create a simple trigger that trims the whitespace on a piece of data prior to it writing to the table. I'm using PL/SQL with Oracle 11g. I am receiving the following error: ...
0
votes
2answers
20 views

Error: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table

I'm trying to run this query in mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1 but got this error all the time: CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ...
2
votes
1answer
170 views

Generic Trigger on MYSQL to replicate data on other table

I'm currently doing a history table that basically logs every single modification in a history table, and adding the Action executed. CREATE TABLE `competencies` ( `id` int(11) NOT NULL ...
0
votes
1answer
3k views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
2
votes
1answer
31 views

How can I pass object to procedure from trigger? [on hold]

I am using mysql 5.6. After insert or update, inside the trigger I am calling procedure, So how can i pass NEW and OLD object from trigger to procedure ?
0
votes
0answers
34 views

Oracle trigger after insert error ORA-01403: no data found

I'm trying to make a trigger that select a value inserted in other table and concat with other value in an insert on other table that's my code CREATE TABLE "SEX" ( "ID" NUMBER, "CS" ...
0
votes
1answer
25 views

Create TRIGGER to insert after the insertion of specific value

I have the following TABLE #1 id time sender receiver message status aux_id TABLE #2 time_to_send sender receiver message aux_id The TABLE #2 has a process that sends the data to an api which ...
1
vote
1answer
33 views

Cannot delete MySQL trigger. Appears in information_schema, but no .TRN file

I have a MySQL database with a few tables and triggers. A process was trying to drop one of the triggers and it failed with the following error: Malformed file type header in file 'trigger_name.TRN' ...
1
vote
2answers
48 views

SQL Server : trigger FOR DELETE

I want to create a trigger, which does not delete a customer if his name is Peter. How can I do that? CREATE TRIGGER trgCUSTOMER ON customer FOR DELETE AS BEGIN IF (SELECT name FROM deleted) not ...
0
votes
0answers
21 views

PL/SQL Triggers [migrated]

I am using these tables: flights (flno, origin, destination, distance, departs, arrives, price) aircraft (aid, aname, crusingrange) employees (eid, ename, salary) certified (eid,aid) and I need ...
4
votes
3answers
17k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
0
votes
1answer
169 views

Issue with a where clause by using Oracle

I'm currently facing a problem while I'm adding a trigger to the database that I use at home. I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production The problem ...
0
votes
1answer
187 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
0
votes
3answers
45 views

Will an update in my trigger trigger itself? Oracle PLSQL

Here's my PLSQL trigger: CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN FOR EACH ROW DECLARE var_root number := :new.ROOT_ID; var_par number ...
2
votes
2answers
174 views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
1
vote
0answers
27 views

Mysql Trigger use inserted value as column name for update After Insert

I have 2 tables: One for storing order_products and another one for keeping inventory levels for multiple stores The order_products table keeps the ID for the store in the store_id column. ...
0
votes
2answers
467 views

Error in trigger: table X is mutating, trigger/function may not see it

Having looked at the following Trigger, still getting the Row 2 error: "ORA-04091: table SUMMITTEST.APL_LOG is mutating, trigger/function may not see it" Please can anyone provide any help in ...
4
votes
1answer
105 views

In SQL Server Mgmt Studio, why don't triggers automatically get updated when tables get renamed?

I ran into an issue last week where we renamed a bunch of tables in a SQL Server and it broke a bunch of triggers. I would have though SQL Server Mgmt Studio would have been smart enough to update ...
0
votes
2answers
457 views

Mysql trigger not working, syntax issues

I have this trigger CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END; mysql 5.1.72-0ubuntu0.10.04.1 ...
1
vote
0answers
96 views

SQL Server inserted/deleted tables [closed]

I have the following logic flow Start transaction insert record 1 into table1 insert record 2 into table1 insert record 3 into table1 commit transaction I have a trigger on table1 that is using ...
1
vote
1answer
20 views

inserting the table colomn data in multiple tables colomn.(SQL SERVER) [closed]

I want to insert one table colomn data into multiple tables colomns using triggers. For Example: I have one table name 'T1' having multiple colomns like 'C1','C2','C3' and now I want to insert these ...
0
votes
1answer
207 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
5
votes
3answers
802 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on the database, that's gives me error. Transaction (Process ...
1
vote
2answers
910 views

SQL Server 2012, triggers missing after 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 ...
0
votes
0answers
59 views

Access Sql Server Linked Server during Logon Trigger

I am trying to log users logons to my Sql Server (A) in another Sql Server (B, different location physically), but when I try to log in I get the error Login failed ... due to trigger execution and ...
0
votes
0answers
77 views

Linked server performace issue?

I have created a linked server in SQL Server 2008 R2 Enterprise Edition for MySQL i.e. MySQL database is accessible under SQL Server environment. I have a created a trigger in SQL Server such that ...
0
votes
1answer
371 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
3answers
279 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
2
votes
3answers
101 views

How to prevent a PostgreSQL trigger from being fired by another trigger?

I have 2 triggers on one table; one works for INSERTs : CREATE TRIGGER "get_user_name" AFTER INSERT ON "field_data" FOR EACH ROW EXECUTE PROCEDURE "add_info"(); This updates some values in the ...
0
votes
1answer
108 views

alternative to trigger. Cascade

I have 2 tables: table_a: id int, user_id int, job_id int and table_b: id int, user_id int, job_id int I would like achieve something like when I delete a row from table_a it also delete a row ...
1
vote
2answers
60 views

How to prevent a trigger from running on certain statements?

I'm looking for an answer that satisfies my need on both SQL Server and MySQL. I have a small, custom audit table that I will be writing to through triggers on various tables. There are situations ...
0
votes
1answer
48 views

The statement has been terminated

I have a table for which a update stored procedure has been created. For this table I created an update trigger. But when I either execute the stored procedure or execute an update query I am getting ...
0
votes
2answers
51 views

PostgreSQL: how do I list all triggers that use a specific field?

for example i have a student table with (studentid) i want to get a list of triggers that use this field... for example if in table X there is a trigger that uses studentid i want it to be shown in ...
1
vote
1answer
214 views

Mysql - Select To XML

I'm Building Audit Trail with Trigger, and want to convert the inserted / updated / deleted value into XML and store it into another table. my trigger look like this: DELIMITER $$ CREATE ...
0
votes
1answer
40 views

Mysql REPLACE INTO query for Multiple rows insertion

I'm trying to add multiple rows using 'replace into' , but its updating one row. REPLACE INTO user_balance (user_id,acc_type,currency,balance,enable_deposit, ...
-1
votes
1answer
24 views

how to use trigger to set value based on query result

would appreciate any example on how to set value on the added row based on query results from different table. something like this delimiter | CREATE TRIGGER trigger BEFORE INSERT ON table1 FOR ...
0
votes
0answers
27 views

Loop through fields on a before-insert trigger?

Here's my table: CREATE TABLE foo ( i_am_pk CHARACTER VARYING(255) PRIMARY KEY NOT NULL, dont_touch_this CHARACTER VARYING(255) NOT NULL, name CHARACTER VARYING(255) NOT NULL, addr CHARACTER ...
0
votes
1answer
200 views

Trigger function check if all NEW.* fields exist in another table

In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv". ...
0
votes
1answer
56 views

Oracle trigger error: table %s.%s is mutating, trigger/function may not see it

I am having some issues while trying to use triggers over tables where I need to get the reference of custom data type from a table. I can't seem to get it to work, here are the scripts I am using ...
1
vote
1answer
141 views

SQL Server: update trigger fires before insert trigger

I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to ...
1
vote
1answer
134 views

Trigger Performance - 1 or 2 Triggers?

I have a question about triggers performance. CREATE TABLE [dbo].[_test]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Date] [DATETIME] NULL, [DateYearID] [INT] NULL, [DateQuarterID] [INT] NULL, [Date1] ...
2
votes
1answer
48 views

Periodically update a table with new records from another server

I have databases on two different servers. I need to regularly retrieve new records from a table on server A, and process them in order to use them to update a table on server B (with a different ...
0
votes
1answer
57 views

Postgres Partition Table Trigger inserting duplicate records

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger CREATE OR REPLACE FUNCTION ...
2
votes
1answer
34 views

Trigger Logic (from SQL 6.5 )

I have a database that was originally implemented in SQL 6.5 and has been migrated to SQL2000, SQL2005 and now SQL2008R2 (Compatibility Mode = 10). Through the migration many new tables, functions, ...
9
votes
2answers
103 views

How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t .... Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
0
votes
0answers
34 views

Mysql trigger on insert on another table

I have three tables all three have [product_id] column, what all i want to do is when there is new entry of quantity of respective [product_id] in table A and table B i want show the sum of the ...