Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
1answer
327 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
0answers
29 views

Get the query that caused the trigger to execute

Is it possible with a trigger to get the query or primary id of the query that triggered it? Currently we roughly have: Delete from Table1 where id = 1 (id is the primary id of that table, 1 is ...
2
votes
2answers
367 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 ...
2
votes
2answers
884 views

How to speed up audit trigger?

The task: write a trigger to log all updates within tbl1 table for future audit users' actions. The tbl1 table: id INT(11) y SMALLINT(6) country SMALLINT(6) -- really here ~20 fields but only some ...
1
vote
1answer
53 views

Composite Primary keys and auto increment?

i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways ...
1
vote
2answers
48 views

Trigger performance - UPDATE statement joining on INSERTED

I have a trigger (MS SQL Server) on TABLE_A that fires on an update that looks basically like the following (I've changed the names of the tables/trigger to simplify): CREATE TRIGGER ...
1
vote
1answer
31 views

Copy a single record to a replica table before update using trigger (Oracle 12c)

I want to insert a complete record (before updating original record) to a replica table using trigger. Following are the sample tables: create table customer ( cID number, cName ...
1
vote
0answers
11 views

ORA-00604 (error occurred at recursive SQL level string) hides the real error

My multithreaded program inserts records into a table. ORA-00001 (unique constraint violated) should not be fatal. But prod environment has a complex configuration with read-only schema and the table ...
0
votes
1answer
1k 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
0answers
29 views

INSERT Trigger not inserting values

I have a table called tbemissoes. When there is an insert, I need to execute this procedure : create procedure exec pr_replicaRemessa --49 , 2016 @RemessaNumero int, @RemessaAno int AS Begin ...
2
votes
1answer
161 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 ...
0
votes
1answer
592 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 ...
0
votes
0answers
8 views

How to get old and new values on Redis notification?

I need to implement a feature like: once any specific key in Redis is updated, call a C function with the old and new values as arguments. Currently, I know Redis notification and I could use ...
0
votes
1answer
35 views

Capture the Query Text of a DML Change

It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML ...
0
votes
0answers
33 views

MySQL: Calculations and Triggers across multiple tables

I'm new to sql and I'm having trouble with a calculation involving two of my tables. I have a table, payroll, which looks something like this: CREATE TABLE payroll ( id INT(6) UNSIGNED AUTO_INCREMENT ...
2
votes
1answer
68 views

MySQL running delete trigger on truncate

I have a table that has an on delete trigger that updates another table. THe table I'm trying to truncate has 130,000 rows. I have duplicate schema and data on my dev server as well as the client's ...
0
votes
1answer
108 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 ...
1
vote
1answer
79 views

Oracle Triggers - before any UPDATE or DELETE or INSERT

I am trying to create a database trigger in Oracle SQL Developer that would fire before any UPDATE, DELETE, or INSERT statement occurs on the sessions table. My sessions table: CREATE TABLE sessions ...
0
votes
2answers
37 views

Is there a way to reference a column dynamically within a trigger?

I asked a similar question a few months back and I'm improving upon my solution! I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of ...
1
vote
1answer
130 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 ...
1
vote
2answers
3k 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 ...
0
votes
2answers
27 views

How to Add AFTER Delete Trigger in My case?

I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables 1) The Patients which are currently ...
0
votes
2answers
49 views

Check if the trigger was fired by INSERT or DELETE

I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet. How to check if the trigger was fired by an INSERT or DELETE? Example CREATE ...
0
votes
1answer
12 views

How to run Trigger only on certain updates

I have the following two tables: CREATE TABLE IF NOT EXISTS `Product` ( `ProductID` VARCHAR(16), `ProductName` TINYTEXT NOT NULL, `CategoryID` VARCHAR(3) REFERENCES Category (CategoryID) ...
-1
votes
1answer
40 views

How to read the deleted value using a variable

I'm trying to get @OldValue = (select @fieldname from #del) to give me the value. Instead I get the column name for the result. Is there a way to write this so it can output the value. If I write ...
-1
votes
1answer
30 views

Update log table with all table updates [closed]

I need to update a log table with old and new updated values. Any ideas how can we do this in a trigger? I need to log all inserts and updates and write a custom message in the column like.. "User ...
2
votes
1answer
187 views

MySQL Local Variable Scope and Triggers

I have a couple triggers that automatically update invoice data and set some statuses in the database. However, it appears that if 2 Triggers have variables that are named the same, they get ...
0
votes
0answers
41 views

Knowing the most juicy client!

Good evening! I'm studying business informatics in Paris and I just created an amazing database that will help the supermarket down my street to win the loyalty of its clients. We want to know, ...
0
votes
1answer
96 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
0
votes
1answer
45 views

How to get the deleted value using while looping through the columns

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get ...
0
votes
1answer
66 views

comparing left join and outer apply doing the same thing

I have an update trigger on a table that that insert rows in another database/table the query look like insert into otherdb.table select manyfield from inserted left join (select id, ...
0
votes
0answers
14 views

Track updated columns postgresql trigger?

I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables. Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
-4
votes
1answer
52 views

SQL server automatically update table column [closed]

The default column is now only restricted to having only 1 Y based on the membershipType column. Now, i am trying to set my defaults column able to automatically change to N if there is existing Y. ...
3
votes
2answers
95 views

Can we commit the transaction when the sql server trigger fails?

I am trying to avoid data loss when the insert trigger on a table fails. I am trying this with the following scenario and my code is failing. When an insert happens on the Customers table, I want to ...
5
votes
2answers
2k views

Is there a way to ensure that a SQL Server trigger will be executed?

My supervisor is hesitant to use a trigger in a process because if there is a network interruption at just the right time, the process would not complete. Does SQL Server include the trigger in the ...
0
votes
1answer
112 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
1answer
199 views

After Insert Trigger never fires using Import Wizard

Using Import Wizard I tried importing tables tcc_Block and PROJECT_IDENTITY. These 2 table structures already exist in SQL Server and they are related with ProjectID columns. PROJECT_IDENTITY has ...
0
votes
0answers
66 views

After update triggers that deal with multiple column updates

I want to create a table for audit (audit in the sense of recording whatever a user has done in terms of data manipulation – e.g. update first name, delete contact etc.). For that I need to write a ...
0
votes
0answers
19 views

Create update Trigger

I'm trying to create a trigger that whenever a software package is installed on a PC, the person who installed the software needs to provide data for pack, tagnum, and instdate, and the softcost field ...
0
votes
1answer
10 views

A different number of AFTER INSERT trigger alarms in SQLite and SQL Server

I have trigger CREATE TRIGGER [Foo] AFTER INSERT ON [Bar] BEGIN UPDATE Log SET count = count + 1; END When i execute query insert into Bar (ColumnName) values ('SomeValue1'), ('SomeValue2'), ...
4
votes
1answer
1k 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 ...
2
votes
1answer
88 views

mysql trigger on a federated table

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a ...
0
votes
0answers
5 views

variable declaration and alteration in a trigger [closed]

For instance, I have a table with 3 different columns. Those columns are all defined as varchars. I insert 2 of those columns and i want a trigger, which generates the third column by merging the 2 ...
0
votes
2answers
396 views

how to use trigger to make SQL Job's step success or fail

Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from ...
0
votes
2answers
106 views

MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?

Here's what I'm trying to archive: use a trigger to divide an update query which updates more than 1 columns into several update query and each of the query updates just 1 columns. For example, the ...
0
votes
0answers
20 views

Prevent Update/Insert/Delete based on Date

I have a simple Table that holds financial data with a date. I would like to endure, that historic values are protected to INSERT, UPDATE or DELETE. Example: All values of March 2016 should not be ...
0
votes
2answers
212 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
1
vote
1answer
30 views

Trigger for a particular row?

I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like: CREATE ...
0
votes
0answers
35 views

SQL Server Trigger: Move deleted rows from 4 different tables to an archive table

I have a garage database with 20 tables. 4 of these tables - tCar, tCustomer, tAddress and tTelephone hold data about when we service a car. After a few months I want to be able to delete the data ...
4
votes
2answers
94 views

Is there a way in SQL Server to make a table only able to insert by trigger?

The title is pretty self explanatory but if your'e curious as to why I want this; I want this because I have an archive/log table that stores past values of an active table and due to this I don't ...