Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
2answers
110 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 ...
0
votes
1answer
58 views

MySQL Trigger Update

How do I update 'emailupdate' date with a trigger when 'email' is updated through a script? +------+----------------+--------------+ | id | email | emailupdate | ...
0
votes
3answers
297 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
24 views

How to implement a query insert into BEFORE INSERT trigger syntax

I want to insert data using trigger into 2 tables. I want to implement my query insert to a before insert trigger. Since I was newbie for using MYSQL, I'm confused how to implement it. insert into ...
1
vote
0answers
7 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
2
votes
1answer
49 views

MySQL BEFORE INSERT trigger with IF conditions

In my BEFORE INSERT trigger, I select data, then I check the value if it's null then I do insert data into table A then else into table B. I select data from a table in another database called ...
2
votes
1answer
61 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
1answer
168 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 ...
0
votes
2answers
714 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
34 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
64 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
228 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
69 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
55 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
215 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 ...
0
votes
2answers
63 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
59 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
39 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
34 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
132 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
172 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
26 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
531 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
46 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
154 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
17 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
45 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
117 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
490 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
281 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
138 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
230 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
59 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
23 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
157 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
16 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
217 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
236 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
164 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
216 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
18 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
137 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 ...