Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,142
questions
-2
votes
1answer
12 views
Create a trigger that will prevent any one from updating personal information of others
I'm trying to create a trigger in DB2 that will prevent any one from updating any personal information that does not belong to them I.E. FIRST_NAME, LAST_NAME, EMAIL , PHONE_NUMBER columns. I'm trying ...
0
votes
0answers
12 views
Is my usage of triggers in postgresql json parsing causing a cpu bottlekneck?
I have recently started working with the Path of Exile API (https://api.pathofexile.com/public-stash-tabs) to track Items for sale by users
The API json is 4 layers deep before I get to the items ...
1
vote
1answer
28 views
Postgres: Trigger always uses the same value for now()
I would like to create a trigger that keeps a last-updated time on each row. It would fire on CREATE and UPDATE, and store the current time into a column. I'm following basically the exact recipe from ...
10
votes
1answer
636 views
How to check if a trigger fires on INSERT, UPDATE or DELETE statements?
How do I determine whether a trigger is targeting INSERT, UPDATE or DELETE statements?
I've checked in sys.triggers and I can't see anything related to what operation the trigger fires on.
2
votes
2answers
45 views
Using Triggers in SQL Server
new to SQL, I'm looking for a way to setup a Trigger that will update Table B when a new row is inserted on Table A. For basic understanding here are some of the columns in my tables:
TABLE A:
+------...
2
votes
1answer
44 views
Trigger working on more than 50 million records [closed]
I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD.
I need to create a trigger there which will update previous entries flag to false for ...
1
vote
1answer
20 views
MS SQL 2014: Triiger to prevent update if existing value is higher than inserted
I need a trigger that will prevent from updating price value if existing value is higher than inserted. If such operation transaction appears, the trigger should prevent value update but let ...
0
votes
1answer
22 views
Trigger based on linked table
OK I have 3 tables:
client:
ClientID (int)auto-inc
clientName (varchar)
tnt_group:
groupID (int)auto-inc
ClientID (int)foreign-key
tnt_item:
itemID (int)auto-inc
itemNo (int)
groupID (int)...
0
votes
0answers
55 views
How to insert from one table to another from trigger
So i have an after insert trigger to insert some data into a temp table, and i want to add to the end of the trigger the ability to insert the rows from the temp table back to the original table. This ...
0
votes
0answers
12 views
LEFT JOIN on AFTER INSERT Trigger in MariaDB or MySQL?
Is there any simple way to access records related to a new one that is causing a trigger?
I am allocating expenses for vehicle use, by creating a general journal entry via AFTER INSERT Trigger when a ...
-1
votes
1answer
24 views
How to proceed with a transaction even if an AFTER UPDATE trigger fails
I have this stored procedure which makes some GET request:
CREATE procedure [dbo].[alert_open_ordr](
@order_number varchar(max)
)
as
DECLARE @status int
DECLARE @responseText as table(responseText ...
-2
votes
1answer
44 views
INSTEAD OF INSERT does not display changes
This is my table:
CREATE TABLE [MyTable]
(
[GroupId] INT NOT NULL,
[ItemId] INT NOT NULL,
[Data] INT NOT NULL,
PRIMARY KEY CLUSTERED ([GroupId], [...
1
vote
1answer
32 views
Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB
I'm trying to get a column named XXX_LASTCHANGE updated whenever a record is updated.
As the XXX would suggest, this is a dynamic value I want to pass with the trigger definition rather than creating ...
1
vote
0answers
19 views
MySQL Multiple IF statements within after insert trigger fails
I have been working on this for about 2 days and am at my wits end. SOS StackExchange Gurus! Can anybody help me PLEASE?!
Okay, so I've got the following two triggers set up - one works the other ...
0
votes
0answers
33 views
In a trigger, does assigning a column to the “old” value incur a cost? (Oracle) [duplicate]
For the sake of readability, in one of my triggers I have :new.COLUMNNAME := old.COLUMNNAME; alongside the sets for the rest of the columns. Does this statement get removed before runtime? (That ...
0
votes
0answers
21 views
Creating a trigger which stores updated value with old value in separate table
I'm trying to create a trigger which adds an old value to a table with the new value next to it.
For example, if someone were to update their address, I'd like the trigger to add the old address to ...
0
votes
1answer
39 views
Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records
I am loading hundreds of order files (positional flat files), via SSIS, into staging tables, and ultimately into production tables. As part of the staging table population process, I would like to ...
2
votes
1answer
30 views
Convert a row to a nested JSON document containing all relations
In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the ...
0
votes
0answers
21 views
MySQL: how to update a table based on changes to another table with trigger or stored procedure
Because of the limitation of a wordpress plugin I am using I cannot update tables from the website when they are created from JOIN statement. So I created a bigger table with data of all tables. But I ...
0
votes
1answer
25 views
How to insert only one row which is going to update
I want to fire a trigger in Mysql DB before update the row. And I want to insert the entire row value in another table.
My trigger fires, but the problem is, it inserts all the data from old table ...
0
votes
1answer
22 views
Trigger: Insert Into For Update IF Update - but with custom insert based on what type of update?
CREATE TRIGGER [dbo].[Logging]
ON [dbo].[TriggerTest]
For UPDATE
AS
BEGIN
IF UPDATE (ColumnName)
Begin
INSERT INTO [dbo].Logging(
CorrelationID,
DateTimeStamp,
...
1
vote
0answers
27 views
How to switch off statement logging from a trigger in PostgreSQL
I just spent ages working out how to record messages from triggers into a log file. And then nearly as long working out where the logs files were being created.
Anyway, I added this line to my ...
0
votes
1answer
21 views
Understanding trigger behavior
I'm having trouble with a trigger which I can't get my head around.
I have a table called TABLE_A with an AFTER INSERT trigger that executes a procedure. The procedure basically does the following ...
0
votes
0answers
22 views
Postgres using powershell scripts - where do trigger messages go?
I have a windows (10 pro) powershell script that reads messages from an external server and INSERTS them into a postges (v10.5) database table.
The table has an AFTER INSERT trigger defined on on it ...
1
vote
1answer
21 views
two-table-over <+> functional relationship: maintaining referential integrity
I have three tables, the last two having a many to one relationship with the first, written here in SQL:
create table study (
id bigint primary key,
name text
);
create table treatment (
...
0
votes
0answers
28 views
Trigger to insert new row into second table causes “Connection is busy with results for another command”
I have created this trigger to insert new row into table @AL_ORDR_TRACKER after new row is inserted in table ORDR.
CREATE TRIGGER dbo.trgAfterInsertORDR ON dbo.ORDR
AFTER INSERT
AS
BEGIN
INSERT INTO ...
0
votes
1answer
46 views
Create an incremental number with year and month without updating the entire table using a trigger
My situation and code
I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number....
1
vote
1answer
40 views
Can a MySQL trigger include a where clause
I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match.
I have a database that stores animal genealogy. Each row stores ...
0
votes
1answer
9 views
updating associated records' tsvectors when the primary record is updated
I have the following trigger function that updates the object's tsvector column when its details change:
CREATE OR REPLACE FUNCTION customer_search_trigger() RETURNS trigger AS $$
BEGIN
...
0
votes
0answers
59 views
How Insert data to child table in different schema using trigger function and auto updates for these child tables
I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing ...
3
votes
1answer
66 views
Scan on trigger Seek on SSMS [closed]
I'm having a performance issue with a trigger when insert/delete on a table, it scan an index associated to a view, but if I execute the update manually it generate a seek.
I updated statistics for ...
1
vote
1answer
27 views
Does a MySQL dump that contains triggers double data on import?
I have a quick and simple question to clarify. I have medium sized database (400 Gigs) running in MariaDB 10.2 with lots of tables and lots of triggers as well. I am planning to take a mysqldump and ...
0
votes
3answers
28 views
How to prevent trigger from overwriting values that were explicitly set?
I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):
create temp table ...
0
votes
1answer
18 views
How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else
I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far.
CREATE ...
0
votes
0answers
52 views
trigger to update if not exists in table oracle pl/sql
I want to create a trigger PL/SQL for the table: ap_supplier_sites_all (oracle EBS R12), in this table i have below information: vendor_site_id (primary key), vendor_id, attribute4 (varchar250), my ...
2
votes
0answers
45 views
Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction
Is there a way to catch a QUOTED_IDENTIFIER error without failing the transaction?
I have assembled a minimal test case here. The trigger must have SET QUOTED_IDENTIFIER ON because it uses XML data ...
0
votes
3answers
166 views
Check if the value from one table exists in another tables in SQL Server
I have one table (tbl1) with column ID, the values can be duplicated. I have others tables (tbl2, tbl3...) with column ID , values are unique.
I want to write a trigger on insert row in tbl1 and check ...
0
votes
0answers
31 views
How to recalculate and adjust the entire row of time in and time out in sql server?
In SN = 1, I have an original schedule of 09:15:59 with my first time in is 09:50:57 so that I am late about 34 mins. In SN=2, 13:27:05 is my time out. In SN=4, I time in again of 15:40:13 that gives ...
1
vote
1answer
66 views
What is the fastest way to (session) lock table?
I have some triggers to log changes on a table to Log table.
On insert and delete I add row to the Log table and for update I add two rows.
The log table contain identity column, and I want the 2 ...
9
votes
3answers
2k views
How is a T-SQL Trigger that only fires on real changes made?
I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it ...
0
votes
1answer
29 views
Delete Trigger - Linking to a table with a PK made up of three columns
I'm currently writing a number of "AFTER INSERT, DELETE" triggers that will be used to sync data between two identical tables, in different instances. The issue that I'm having right now, is getting ...
0
votes
2answers
46 views
How to convert UPDATE operation into INSERT using triggers
Given I have a table User with column status (active/inactive)
these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified.
and for ...
0
votes
0answers
26 views
Oracle Flashback with triggers
Has anyone added triggers to the table that was established for flashback? The thought here is to capture change data, then add additional data to the row for offloading to another service without ...
0
votes
1answer
20 views
mysql triggers to multiple table and insert new id to table
I have 4 table:
table a (id (auto_increment), id_b , id_c, id_d(foreign_key from table b,c,d) , column)
table b (id auto_increment, column)
table c (id auto_increment, column)
table d (id ...
0
votes
1answer
38 views
Syntax error in simple trigger
I have a SQLite table with these fields among others
Fld_QuoteNumberRoot string
Fld_Revision integer
Fld_QuoteNumber string
I need to fill ...
-1
votes
1answer
26 views
Triggers and Tables with SQL and NodeJs
Hi I 've a application runnning with nodejs and SQL server, What I need is that every time I Insert a record into the ChargesTable I need to check, how may charges does my user have made, and every 5 ...
4
votes
1answer
97 views
Complex Delete Cascade Including Self-Referencing Table
Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
0
votes
1answer
44 views
POSTGRESQL: Refresh the materialized view which effect when there are change based on columns
I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works ...
5
votes
1answer
119 views
Grant dbcreator only for databases matching prefix
In Microsoft SQL Server 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.
Is it possible to do this at the ...
0
votes
1answer
13 views
argument 'and' incorrect, sum values of attributes
excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries?...
