Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
0answers
120 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
16 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
1
vote
1answer
47 views

Trigger in MySQL upon changing stock status

This are one of the requirement that have to be done for my assignment and I am having a trouble on how or where do i start. my lecturer told us smth having a trigger and he is reluctant to guide us ...
0
votes
1answer
51 views

RAISERROR on a trigger. What will happen? Will we get informed?

What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below? RAISERROR('Attempt to modify supposedly immutable number.',16,1) How will we know this ...
1
vote
1answer
27 views

Configure MariaDB to log row deletion ONLY

I'm investigating some rows/tables that intermittently go missing on a Magento site, and of course there's no entries in the error log. (I suspect Magento queries/rules or human error might be the ...
3
votes
2answers
861 views

How to reference only affected rows in AFTER UPDATE trigger

I have this table: And I'm trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number ...
0
votes
0answers
35 views

Increasing system load due to multiple inserts every millisecond

In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows. ...
0
votes
0answers
98 views

Trigger on Update of Specific column with aggregate function

I have two tables. Table DNF and table Programs. I would like to update the Programs table offbudget column only when there is some modification made in the ActualBudget column of the DNF table that ...
0
votes
0answers
23 views

Which trigger inserted data the table

I have 3 triggers which insert data to a table. I would like to find out which one is inserting the data. How do I search. The triggers are on another schema I do not have access to. oracle 11g
3
votes
2answers
231 views

How to capture sp_refreshsqlmodule event

My issue: executing sp_refreshsqlmodule will update the sys.objects.modify_date value, but does not fire a DDL event that can be used by a DDL trigger. So if using a DDL trigger to show object change ...
0
votes
2answers
82 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
0
votes
0answers
63 views

Trigger junction table to closure table on insert without infinite loop

I have a junction table for many to many relationships. I need to construct a trigger on insert to insert into a closure table that will NOT create an infinite loop. the DBMS is MySQL. My concept ...
0
votes
0answers
90 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
1answer
35 views

How do I stamp nothing?

I get an LDAP feed in for my employee database - basically a big csv file. This updates once a night and I revise all of our internal apps based on this spreadsheet. When a new person comes on the ...
0
votes
1answer
79 views

Two Triggers affect the same row

I've have triggers on two separate tables that are designed to affect a third table, the problem is each trigger affects different rows. The third table, C, currently looking like this: 00000010 | ...
1
vote
1answer
222 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 ...
2
votes
1answer
456 views

Instead of insert trigger VS For insert Triggers in SQL server

Edited: My question is since there is an insert statement in the instead off insert trigger, then it should call insert trigger from inside the instead off insert trigger and which should avoid ...
3
votes
1answer
165 views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
0
votes
1answer
123 views

How to profile trigger database performance impact

I want to set up some triggers on a DB2 box (LUW, 9.7). The DBAs are concerned that if the trigger fires too often, it will adversely affect general performance. What's a reasonable cheap approach ...
1
vote
1answer
207 views

How to remove triggers from the restored database in SQL Server

I am using SQL Server 2008 Management Studio. I've got a .bak backup file from a client to resolve a specific issue. Once I restore the database from the .bak file, I am unable to do any alter query ...
1
vote
1answer
5k views

Create Trigger MySql update or insert in another table

I am trying to learn more about triggers in mysql, so bear with me. I have two tables, TestTable (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Date date, Value REAL) TestTable2 (id ...
3
votes
0answers
185 views

Preventing Infinite Trigger Loops in PostgreSQL

I am working with two PostgreSQL databases (one internal and one on our company Web server) where I am attempting to share data between the two instances through the use of triggers. The databases ...
0
votes
0answers
135 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 ...
2
votes
0answers
39 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 ...
0
votes
1answer
108 views

MySQL Trigger Update

How do I update 'emailupdate' date with a trigger when 'email' is updated through a script? +------+----------------+--------------+ | id | email | emailupdate | +------+----------------+-...
2
votes
1answer
312 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 ebsms....
1
vote
1answer
108 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 as:...
1
vote
2answers
235 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 ...
1
vote
1answer
148 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 ...
3
votes
1answer
87 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
220 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 z)...
2
votes
0answers
55 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
54 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
175 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 ...
0
votes
2answers
273 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
1answer
911 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
1answer
120 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
223 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 `trig_before_up_dad_records`...
1
vote
1answer
599 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 fires,...
2
votes
0answers
55 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 ...
10
votes
4answers
535 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
122 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
113 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
307 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
368 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
35 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
81 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
93 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
80 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
49 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) ...