Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
0answers
27 views

Using a trigger to check whether a matching record exists

I have a database model set up as in the picture above, modelling a student's enrolment into a unit with an associative entity to facilitate the m:n relationship. I wish to code a trigger that will ...
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
0answers
14 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 ...
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 ...
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 ...
0
votes
0answers
9 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
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 ...
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
28 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
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 ...
2
votes
1answer
70 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
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) ...
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 ...
-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 ...
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
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
53 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. ...
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 ...
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 ...
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'), ...
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
0answers
68 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
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 ...
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
1answer
97 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
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 ...
2
votes
1answer
37 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
9 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 ...
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 ...
0
votes
1answer
38 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 ...
1
vote
1answer
45 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 ...
0
votes
1answer
39 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
61 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
44 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
51 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
1answer
57 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
48 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 ...
0
votes
1answer
54 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
61 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
58 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
44 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 ...
0
votes
1answer
37 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 ...
1
vote
1answer
25 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
44 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 ...
0
votes
1answer
93 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 ...