Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

-1
votes
1answer
15 views

Triggers in MYSQL

I got a requirement like, i need to move the values from one table 1 to another table 2, it should only when table 1 rows exceed the value of 9. once i insert the 10th row to the table 1, old 9 rows ...
0
votes
2answers
687 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 ...
1
vote
1answer
27 views

Oracle: Mutually modifying two tables?

Suppose I have two tables I want to keep in sync: create table a(x number, s varchar2(10)); create table b(x number, s varchar2(10)); It's easy to keep B synchronized with A, using triggers such ...
1
vote
2answers
51 views

MySQL - How to make a DATEDIFF in years?

This is my first post (and English is not my mother tongue) so I hope I won't screw this post up. I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed ...
10
votes
5answers
18k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
1
vote
1answer
226 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 ...
1
vote
1answer
55 views

Determining the specific command type in a DDL trigger

Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is? I have a DDL trigger on a database that will be fired ...
0
votes
1answer
46 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to ...
2
votes
2answers
209 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
1answer
43 views

Trigger function using current row and current table name as variables (final part)

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, they only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... ...
0
votes
2answers
56 views

How to know which user update/insert or delete in MYSQL database?

Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database. My problem is that I want to know which user made ​​the change in ...
1
vote
3answers
52 views

PostgreSQL trigger function that selects from %current% table

I have multiple tables that have the same column names, they only vary in their column values, like: tbl_log_a tbl_log_b tbl_log_c ... 26 tables from a to z. Each table has a trigger that calls a ...
2
votes
0answers
15 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
0
votes
1answer
17 views

How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

I want this trigger to be fired after an insert is made with the text, this is what I have so far ALTER TRIGGER [xxx] ON [dbo].[Main] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT ...
3
votes
3answers
3k views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
1
vote
1answer
37 views

Mysql triggers for auditing

We have a requirement to fulfill - we need an auditing table for all of our entities. We wonder if creating the following triggers will be a good practice: CREATE DEFINER=`root`@`localhost` TRIGGER ...
0
votes
1answer
32 views

Mysql create trigger if not exist

I have a script that creates triggers, I would like to run it multiple times and if a trigger already exist it needs to skip the creation part. Is there a "create if not exist" in mysql triggers? I ...
1
vote
0answers
43 views

create a trigger to alter table add column based on inserted row [migrated]

I have two tables in a database named as follows: 1. state_master +---------------+-------------------+ | state_id | state_name | +---------------+-------------------+ | 1 | ...
0
votes
1answer
126 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
1answer
168 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
1answer
23 views

MySQL Trigger On UPDATE “Unknown Column” Error

I want to log a table when its updating a row which is column "insertDate" or "updateDate" value is before than 3 month. I wrote this trigger: DELIMITER /// CREATE TRIGGER ...
1
vote
1answer
31 views

Trigger to apply user mapping to logins on database creation [closed]

Does anyone have any example triggers that apply user mapping on database creation?
2
votes
1answer
148 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] ...
0
votes
1answer
521 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, ...
1
vote
1answer
42 views

SQL Server After Insert trigger does not perform actions

I have a problem with 2 After Insert triggers and 1 Instead Of Insert trigger. The After Insert triggers should perform some actions but that actions are not fired. When the Instead Of trigger ...
9
votes
4answers
145 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 ...
1
vote
0answers
16 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 ...
0
votes
1answer
44 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 ...
2
votes
1answer
114 views

Prevent row from being inserted with a trigger

I'm trying to prevent a row being inserted if a certain condition is met using a trigger. I'm using a user defined exception to do this, however the row is still being inserted, despite the exception ...
4
votes
2answers
484 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 ...
0
votes
1answer
238 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, ...
0
votes
1answer
133 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
1answer
222 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 ...
6
votes
2answers
55 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 ...
1
vote
0answers
22 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 ...
1
vote
1answer
156 views

Triggering a shell script from MySQL without any 3rd party libraries

Is it possible to call a bash script file in linux using a trigger in MySQL? I've been searching for the answer online and I ran into this articles: ...
1
vote
0answers
14 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
23 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
2answers
194 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
234 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
2answers
153 views

How to use a trigger to only insert one row

I am doing a third year project using an intel galileo that reads an RFID card, and signs a student into a class, and I am using multiple tables. I have nearly completed it as all reads are been ...
0
votes
1answer
214 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
296 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 ...
0
votes
0answers
16 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 ...
4
votes
2answers
134 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
0answers
32 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
32 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 ...
0
votes
1answer
158 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 ...
1
vote
0answers
20 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) ...
1
vote
2answers
2k views

Updatiing a column after expiration of specific time in MySQL

I have a table where I have a column named state(int(2)) and modify_time (time_stamp on update current timestamp). I have an update trigger which changes the value of state column to 0 based on some ...