Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

2
votes
0answers
47 views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
9
votes
4answers
451 views

Is it poor practice to aggregate data from different tables into one?

Background I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different ...
0
votes
1answer
110 views

Locking because of triggers mysql

I have a memory table and I am updating it somewhere around 3000 updates per second. I setup a another table in Innodb and then created a Trigger so that all data can be send to the innodb table as a ...
1
vote
0answers
104 views

Migrating schema from Entity-Attribute-Value (EAV) model to proper tables

I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible. Existing ...
8
votes
2answers
248 views

Tracking current user through views and triggers in PostgreSQL

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
2
votes
0answers
254 views

Cannot update parent rows with trigger on the same table

I currently have the following two tables in my database CREATE TABLE folders ( id NUMBER NOT NULL, name VARCHAR2(100) NOT NULL, bytes NUMBER DEFAULT 0 NOT NULL, parentId NUMBER, CONSTRAINT ...
0
votes
1answer
34 views

What could cause a record to appear in the MySQL binary log but not the original table?

I have a web application (CiviCRM) that has the option to enable binary logging, which I've done. We use this software to record donations, including automatic recurring donations. Our payment ...
0
votes
0answers
66 views

Trigger for different database host and remote server in MYSQL

I have two database server running on different host(Old host server,new host server). I want to create trigger i.e. If I insert any new record in new host server's table then that record is also ...
0
votes
0answers
87 views

Creating a trigger to update field in 1 table based on values in another table using data in multiple rows

I'm new at SQL and need to create 2 triggers between 2 tables. Table 1 has the following columns: tbl1.ContractID char(6) tbl1.ContractFulfilled char(1) Table 2 has the following columns: ...
2
votes
0answers
73 views

Options for copying data out of continuously updating tables

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business ...
1
vote
0answers
45 views

trigger to first validate values in other table and then update

I have problem coding a trigger. The table structure is this: table 1: inventory field 1: item_code(PK) field 2: item_desc field 3: unit_price table 2: stock_master field 1: item_code(fk) ...
4
votes
2answers
210 views

Fire and Forget SQL Triggers?

I have a 3rd party app that uses SQL Server as a back end. Specifically we have it on SQL Server 2000, SQL Server 2008 and SQL Server 2008R2. Any solution must be available to all, but solutions for ...
0
votes
1answer
98 views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
2
votes
1answer
118 views

Delete trigger mysql on another table

I am new on mysql and I am try to create a before delete trigger on mysql. I have a table call "persons" and I need delete a record on another table called "ldap_entries" the table "persons" have a ...
1
vote
1answer
33 views

Why my BEFORE UPDATE trigger takes values from the NEW record? [closed]

I'm updating data with QGis on a Postgres table. The operation consists of splitting a polygon in two parts, which means that QGis updates the original polygon (to become one of the 2 new ones) and ...
1
vote
1answer
59 views

What is the syntax to sp_recompile logon trigger?

I would like to recompile my logon trigger using sp_recompile. But I can't figure out the syntax. E.g. these don't work: sp_recompile '[Tr_Logon]' sp_recompile '[Tr_Logon]' ON ALL SERVER ;)
1
vote
1answer
125 views

Do Triggers in the Active Secondary Database of an AlwaysOn Group Execute?

If I have triggers on tables in a database that is a member of an AlwaysOn group, will they fire on the secondary? If they do fire, and they do something different on the secondary will it matter as ...
4
votes
2answers
682 views

How do I find the next saturday in MySQL

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP. So lets say, it is Wednesday (2015-7-22) today. If my ...
1
vote
1answer
268 views

mysql trigger / stored procedure - update another table row by row

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this. this will be a little long, be patient please. First of all, I am trying ...
2
votes
1answer
154 views

SQL Server 2008 Triggers and Merge Replication

I am trying to have a trigger fire when the Merge agent inserts or deletes data into / from a table. It is supposed to insert data into a report table for use with SSRS. I DO NOT have the "NOT FOR ...
3
votes
0answers
530 views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
1
vote
1answer
512 views

how to use a variable as a part of a field name in mysql trigger declaration [duplicate]

Here is what I want to do (a part of my trigger): BEGIN DECLARE updatecount INT default 0; declare number int; declare column varchar(10); declare i int default 1; declare j int default 1; while ...
2
votes
1answer
6k views

The user specified as a definer ('root'@'%') does not exist

I have a trigger defined as follow: USE `veeva_bi`; DELIMITER $$ DROP TRIGGER IF EXISTS veeva_bi.account_ai$$ USE `veeva_bi`$$ CREATE DEFINER = CURRENT_USER TRIGGER `veeva_bi`.`account_ai` AFTER ...
2
votes
1answer
55 views

Using an UPDATE trigger to populate Eastings and Northings in PostgreSQL

I am wondering if it is at all possible to create trigger that populates the X and Y of a created point into 2 separate fields called 'easting' and 'northing'? It is an update trigger on a table ...
5
votes
0answers
115 views

Server Logon Trigger fails after deleting many records in “Logon-Table” -SQL Error 2801

We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table. The table doesn't have clustered index, but it does have a ...
1
vote
0answers
36 views

Mysteriously Updated Rows

Here's the trigger causing the problem: USE [SalesDb] GO /****** Object: Trigger [dbo].[trgAfterUpdate] Script Date: 7/16/2015 6:34:01 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ...
2
votes
1answer
162 views

MYSQL: log the query executed on certain table

I am writing a trigger that suppose to fire on inserts for a table and log the exact query used to insert. However, using below SELECT info INTO original_query FROM INFORMATION_SCHEMA.PROCESSLIST ...
5
votes
1answer
78 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
1
vote
1answer
113 views

executing command prompt command in MySql trigger

I have one table called table1 in that there are two fields, let say field1 and field2. What i am trying to do is that, when there are records inserted into table1, the values of field1 and field2 get ...
3
votes
1answer
762 views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
3
votes
1answer
206 views

Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using ...
1
vote
1answer
111 views

How to update row values with values from SELECT into another DB in a trigger

I have been working in a trigger for INSERT|UPDATE values for a given record based on same PK. What trigger basically should do it is, foreach row when some INSERT happens on veeva_new.account: ...
0
votes
1answer
268 views

DB2 Trigger Error : An unexpected token “OR INSERT” was found following SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

I am getting an error while executing the following queries. As per the DB2 documentation, we can have both update and insert event in the same trigger, but when I am trying to do so I am getting an ...
1
vote
1answer
32 views

Update reps table with right values in a trigger after insert on userterritory

I two DBs veeva and pdone as shown below in the pics As you can see veeva is simple and hasn't no constraints which relies on duplicate and wrong values. The problem happen exactly on ...
2
votes
2answers
59 views

Trigger Gives an Error on Multiple Rows SQL SERVER

I have a problem when I create a trigger which is giving me this error: Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, ...
1
vote
2answers
391 views

how to create trigger in SQL server using two tables to find stock quantity

I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table. There is kikkat as a name n ...
1
vote
1answer
25 views

Trigger is failing with no errors at all, why?

I need to UPDATE territory_id column at pdone.reps table by using and filtering results from veeva_new.userterritory so based on the answer I got here I am trying to build my own and this is how it ...
1
vote
2answers
105 views

Postgresql: Insert trigger function fails on partial insert statement

We have some big tables in our database and the boss thought that partitioning would be a good improvement to our system. I was charged to investigate it and develop all the needed scripts in a test ...
1
vote
0answers
310 views

How Columns_update() Function Works in Trigger To Find Updated Columns Name

I am in confuse about trigger function Columns_Update(). i Have Basic Idea about this function that it returns a varbinary and it is use to get updated Columns in Trigger even i have Find The code ...
3
votes
0answers
85 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
4
votes
3answers
631 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, ...
0
votes
0answers
64 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 ...
1
vote
0answers
61 views

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

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: ...
1
vote
2answers
1k 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 ...
0
votes
0answers
911 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" ...
2
votes
1answer
135 views

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

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
1answer
98 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
179 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' ...
2
votes
1answer
149 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
2answers
699 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 ...