Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
7
votes
1answer
282 views

Sometimes the updated rows are not locked within instead of update trigger

Here is as small repro: create table dbo.t (id int primary key, v int); insert into dbo.t values (1, 1), (2, 2); create table dbo.s (id int primary key, v int); insert into dbo.s values (1, 10); go ...
0
votes
1answer
21 views

How to issue a DML with WHERE clause comparing a value which is trigger generated in table?

Suppose I have a table with CREATE, CREATE TABLE TEST ( year int, month int, date int, hr int, min int, sec int, timestamp timestamp, value double ); CREATE ...
0
votes
0answers
732 views

Executing 'GRANT SELECT' in a trigger possible?

Trying to grant select privilege on table USER1.AAA to USER2 when table USER1.AAA is created, using system trigger. The trigger below seems to be processed when table AAA is created but it receives ...
0
votes
1answer
39 views

Why do I need DAC if I am able to connect to the server via sqlcmd?

DAC attracted my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned ...
2
votes
0answers
116 views

Postgres 11: fastest way to convert a small list of numbers to text (to be used as NOTIFY payload)

I am on Postgres 11.5 and in a trigger function I issue one or more pg_notify calls for each row modified in the transaction. The most common payload is a list of 12 float numbers, representing two ...
-3
votes
1answer
133 views

Transfer Mysql table data to client Server via TCP/IP by using Trigger [closed]

i Have Database on MySQL Server. I have one Table so called "log" in Database. Data has been insert in Database table automatic. I would like to transfer table via TCP/IP to the another Software. ...
1
vote
2answers
639 views

Setting NLS_LENGTH_SEMANTICS for session via trigger

I have the following problem with my Oracle 12c database. I have to install a third party application, which needs an Oracle database instance. The supplier demands to set the NLS_LENGTH_SEMANTICS ...
1
vote
2answers
828 views

Ignore a particular column on update trigger

I have a PostgreSQL table with 15 columns. I have an on update trigger on it. I'd like to exclude column10 from it. Meaning when there is any update on column10 only then there should not be any ...
0
votes
1answer
272 views

MySQL Trigger: Insert depending on NEW.column of type enum

I am trying to add a trigger to one of my tables. Depending on a enum-value of the newly inserted row it should execute a different insert-query. I am using phpMyAdmin and am trying to execute my ...
1
vote
1answer
28 views

Insert as many rows as count on other table returns

Hello I have three tables in my MySQL-DB Project: id | name 1 | project1 2 | project2 KPI: id | name 1 | build_status 2 | satisfaction ProjectKPIData: id | kpi_id | project_id | value 1 | 1 ...
0
votes
1answer
43 views

BEFORE INSERT validation trigger for GPS coordinates

I have a MySQL database table that contains zipcodes and their corresponding lat/lng values. I have another table that contains business locations (including lat/lng) Prior to inserting new records ...
1
vote
1answer
972 views

Triggers on partitioned tables on Postgres 11.5

I asked a question about History table design for deletions in PG 11.5, and received a suggestion to partition the table. This is an excellent idea as the table may become huge, and the information ...
2
votes
2answers
79 views

History table design for deletions in PG 11.5

I've got a question about the design of a history table in Postgres. The setup is that I've got a table that holds a needs list. A location recalculates in-demand items every five minutes, and pushes ...
0
votes
1answer
196 views

Trigger function error: Record "old" not yet assigned

I'm new to trigger functions and trying to work this out. I have looked for solutions in the documentation and applied those, but still something is wrong here. My aim is to show error an message when ...
0
votes
2answers
102 views

Variables in Oracle Triggers

Context Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between ...
1
vote
0answers
110 views

Capture trigger update() with inserted & Deleted changes only

I have price table that has over 100 columns. I'm in the process of fine tunning triggers and reducing amounts of records inserted, to audit changes when updates are applied. The audit table ...
0
votes
2answers
49 views

on deleting a row, the update logic is run also

I am using a after insert, update delete trigger. When I insert a record in my application called as clarity, it inserts a row in the test table with the value as 'Insert' When I update the record ...
-1
votes
1answer
175 views

MYSQL Trigger: Update before insert

I want to increment the order of every video by one every time a video gets inserted so that the new video takes its place UPDATE services SET orders = orders + 1 This doesn't work, I get this error,...
0
votes
0answers
119 views

Trimming copy/delete mysql table rows using Trigger

I am using Freeradius AAA server on Ubuntu 18 server which uses mySQL ver 5.7.27 as DB to store entries. INNODB is being used. It have a table called radacct which stores users accounting /session ...
0
votes
1answer
39 views

Avoiding deadlocks when using trigger

I have a database with a table called subscribers. The engine is InnoDB. The table has approximately 100,000 rows. This table contains multiple columns: CREATE TABLE `subscribers` ( `id` int(11) ...
1
vote
2answers
863 views

Azure SQL Server Trigger Not Firing

Using Azure SQL Server, on a managed instance (i.e. a single server running multiple databases, where the db in question is one of them). I've created a trigger on INSERT to table A, that performs an ...
3
votes
1answer
58 views

Duplicate Inserted Row into the new table when running trigger [closed]

I create 2 tables like this: CREATE TABLE task ( task_name TEXT NOT NULL, duration INT, UNIQUE(task_name) ); CREATE TABLE temptable ( task_name TEXT NOT NULL, duration INT NULL, ); Then I ...
5
votes
2answers
4k views

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it: -- ...
0
votes
0answers
303 views

update trigger insert only if value is different

i have some gps modules that send data to my database. On update of the last package sent i want to compare new values with old values and insert into a new table only those values that are diferent. ...
-3
votes
1answer
42 views

Create a trigger that will prevent any one from updating personal information of others [closed]

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 ...
1
vote
2answers
1k views

Trigger function does not exist, but I am pretty sure it does

I have a procedure: create or replace procedure pro_update_last_read(in input_sensor_id integer, in read_time timestamp) as $$ begin update sensor set last_read = read_time where ...
0
votes
0answers
57 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
223 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 ...
11
votes
1answer
1k 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
68 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
61 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 ...
2
votes
1answer
98 views

Trigger to prevent update if existing value is higher than inserted value

I need a trigger that will prevent from updating the price if the existing value is higher than the inserted value. If such operation transaction appears, the trigger should prevent value update but ...
0
votes
1answer
24 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
64 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
80 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
194 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
2answers
64 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
706 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
524 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
38 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
421 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
1k 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
104 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
26 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
25 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
142 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
40 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
673 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
30 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 ( ...

1
3 4
5
6 7
28