Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
0answers
47 views

After update triggers that deal with multiple column updates [duplicate]

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 ...
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
82 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
4 views

variable declaration and alteration in a trigger

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 ...
-1
votes
0answers
12 views

Trigger to calculate one column from other table [on hold]

I have a table called employee that has these columns: id Fname lname super_id department_Num I also have another table called department, which has these columns: dep_num name manager_id (FK → ...
0
votes
2answers
385 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
98 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
25 views

Function - PostgreSQL 9.2

On the table users there is a code column: code CHARACTER VARYING, The customer can add any value he wants into that column, but he also can chose by not adding any value. That column can't be ...
0
votes
0answers
12 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 ...
-1
votes
1answer
55 views

UPDATE statement in a trigger occasionally updates some columns but not others [closed]

I have a table called NOTATION with the below table structure: ( NOTATION (primary key) TABLE_NAME , TABLE_KEY , NOTE_TIMESTAMP, SUBJECT , COMPANY , ...
0
votes
2answers
201 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
26 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
32 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 ...
3
votes
2answers
79 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 ...
1
vote
1answer
112 views

MySQL After Update Trigger apparently not running

I have the following After Update trigger on a MySQL table: CREATE DEFINER = 'root'@'localhost' TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD AFTER UPDATE ON ...
2
votes
1answer
34 views

Permissions on Trigger Problem

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem. The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the ...
0
votes
0answers
8 views

Execution order of triggers for different tables in SQL Server 2008

There is a lot of material that covers the case where there are multiple triggers for one table but what about the case where there are two related tables, each with a trigger, e.g. an update triggers ...
0
votes
2answers
4k views

Update a column with values from the same newly inserted row in a trigger

Using SQL Server 2014, I have this: Table1 Tab1_id int identity(1,1) primary key Tab1_val1 smallint Tab1_val2 smallint Tab1_valInfo varchar(10) -- not yet implemented, will be used ...
1
vote
1answer
9k views

MySQL trigger - delete data from multiple tables

I would like to delete data in multiple tables, from a trigger but it seem like my syntax is incorrect. I got the error: "you've got a error in your MySQL syntax please check the manual". This ...
0
votes
1answer
32 views

mysql CREATE TRIGGER after insert on Update column with count

it gives error. I don't understand why. CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate` FOR EACH ROW BEGIN UPDATE estate_category set piece = (Select count(*) from estate where ...
0
votes
1answer
47 views

using triggers to synchronize data

i've two database, Database1 and Database2! i'd like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I've used triggers to synchronise data and it ...
1
vote
1answer
42 views

Creating an Updating trigger

I have to create a trigger that calculate the the quantity in stock and this is my first one on PostgreSQL. When entering the quantity bought at a Provider (Fournisseur) or a delivery at a client, ...
0
votes
2answers
202 views

Trick for AUTO_INCREMENT on composite keys in InnoDB

In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT. For example consider the following table: CREATE ...
1
vote
1answer
34 views

How to use trigger in VoltDB

As I know, trigger isn't supported in VoltDB. If anyone has updated news about trigger on VoltDB for nowadays, would you please let me know. So my question: is it possible to implement a ...
2
votes
2answers
357 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
1answer
35 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 ...
1
vote
1answer
56 views

Oracle: Query gives different answer when running from procedure

I guess my problem is somehow related with grant permissions. I have an "ADMIN" user, I grant it "dba" permissions like this: grant dba to admin Now in the "ADMIN" schema I have 2 tables: The ...
3
votes
1answer
40 views

How to maintain trees and their roots in SQLite

I have a (SQLite) database with a table representing nodes in a forest (set of trees): create table "Node" ( "id" Text primary key, "parent" Text references "Node" ("id") -- parent node ...
1
vote
1answer
114 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 ...
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 ...
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
1answer
540 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
51 views

SQL Server Trigger When New Value <> Old Value

I need a trigger that update to Null a column averytime another column is changed. This is what I have now: ALTER TRIGGER [dbo].[ERROR_CLEAR] ON [dbo].[contact] AFTER UPDATE AS BEGIN SET NOCOUNT ON; ...
0
votes
1answer
323 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 ...
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 ...
1
vote
1answer
168 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
1answer
48 views

Database design for a lot of rows with data archive

i'm new to database design on larger scale. I have some knowledge and experience with MySQL but now i have some performance issues. Currently i'm using MySQL with 3 databases: History Snapshot ...
0
votes
2answers
52 views

How to create an insert trigger?

This is 1/4 of an overall assignment and I need help with this part. The assignment question: Create a trigger on the Rental table that will check when a new rental is to be inserted, that the ...
0
votes
3answers
44 views

How to get currently inserted rows and sum them within Mysql trigger before insert

I have a mysql trigger that fires BEFORE INSERT. My php application is inserting multiple rows at once like so INSERT INTO table1(id,name,price) VALUES(1,'LH32400', 1990),(2,LH32401,2000); The idea ...
1
vote
1answer
24 views

Paged query against timestamp populated by trigger missing items

We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out. First some background. We have a "tombstone" table that is populated using a ...
0
votes
1answer
168 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 ...
2
votes
1answer
196 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
34 views

Trigger with multiple inserts based on JSON data

I am using sqlite and have its json1 extension available. I have the following tables (reduced to a minimal example): -- table filled from external source, -- with only a little possible impact on ...
0
votes
1answer
39 views

Alternative of trigger to insert updated date in a column

I am looking for some best practice to insert the update date value in column whenever update query is run without using any trigger. So could anyone suggest me is there any best way except using ...
1
vote
1answer
35 views

Read only postgres table, except by trigger

Suppose the following situation: I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock ...
1
vote
4answers
52 views

User privileges error on server maria db Create trigger Drop trigger

User privileges error on server maria db User : user1 DB : db1 Error 15:00:46 DROP TRIGGER IF EXISTS audit_trigger Error Code: 1419. You do not have the SUPER privilege and binary logging ...
0
votes
2answers
97 views

Can a trigger catch updates done by a select for update + lob.write()?

It's maybe a stupid question, but I didn't find an explicit answer... I want to catch any update on my oracle db, so I want to use triggers, but the triggers not been activated when using SELECT FOR ...
2
votes
1answer
2k views

Testing AFTER INSERT Trigger

I have the following table: CREATE TABLE Train( Train_No integer PRIMARY KEY, Loco_No integer REFERENCES Locomotive(Loco_No), Back_Loco_No float REFERENCES Locomotive(Loco_No), ...
0
votes
1answer
74 views

How to commit transaction on an after update event trigger?

I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance. I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that ...
0
votes
0answers
35 views

Query hung or way too long?

As part of the proccess of new items added to our system, some tables and relations are created programmaticaly via trigger, but it seems to be failing quite often right now. The DDL seems to be ...