Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
0
votes
2answers
17 views

PostgreSQL 11 trigger can't select a value in a table to use it in a second table

Here's a sample of log : gid alti numlog 1 79.55 1 2 79.65 2 Here's a sample of us : gid numlog numus sommet base alti_sommet alti_base 1 1 1000 0 0.25 (79.55-0) (79.55-0.25) 2 ...
0
votes
1answer
39 views

Duplicate record inserted - why trigger is not preventing it?

I have two tables: DebitIUVs and RPTs. I have record inside DebitIUVs which is with state 118001. My business logic must insert new line inside RPTs table. Inside RPTs table there is additional ...
0
votes
0answers
72 views

Bad bind variable error [closed]

I have this trigger: CREATE OR REPLACE TRIGGER IGUALFECHPEDYGAR_VT BEFORE INSERT OR UPDATE ON GARANTIA_VT FOR EACH ROW DECLARE FH DATE; BEGIN SELECT FECHA INTO FH FROM pedidos_vt WHERE oid_p1=:NEW....
0
votes
1answer
19 views

Table Partitioning Using Inheritance and Triggers

I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I ...
0
votes
1answer
27 views

trigger to update row number

I have a MariaDB table id | key | year | .... | position 1 | key1 | 2004 | ... | 3 2 | key1 | 2011 | ... | 2 3 | key1 | 2017 | ... | 1 4 | key2 | 1994 | ... | 2 5 | key2 | ...
0
votes
0answers
30 views

MySQL trigger: use NEW as a table

In a trigger we may refer to inserted/updated values use NEW alias as a table. But it is NOT a table. I insert some data into some table, and I need to update some another table during this insert. ...
0
votes
1answer
31 views

Is it possible on insert into a voucher table to update the amounts into the ledger table

Most answers focus on setting a flag or inserting a row in another table my but the task I am trying to accomplish is a bit different. I have a voucher table with the following structure +-----------...
0
votes
2answers
12 views

Is there any way to force at least one column NOT NULL

I have a schedule_days table that is having id, day, date columns. id - auto increment, primary key day - weekday - enum(weekdays) default not null date - a specific date - default not null ...
0
votes
1answer
51 views

Do triggers start to degrade in performance (or sometimes fail) on highly transactional tables? [closed]

How many transactions per second can SQL Server table triggers handle? If I had a highly transactional table that was inserted into 1,000 times a second, are insert triggers on that table reliable to ...
0
votes
0answers
39 views

How to create and process queue of data (FIFO) from MS SQL Trigger

I would like to create a table and fill it with data and have it act as a queue that works in a first-in-first-out fashion. New items can be added at any time while processing or not. My inserts ...
6
votes
1answer
197 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
18 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
43 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
0answers
13 views

Trigger on update of any columns to copy the update to another able

I am partitioning an existing table in production called app_users, to do this, I have created a partition table app_users_partition with it's partitions along with a trigger in app_users which copies ...
0
votes
1answer
33 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
36 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
47 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
votes
1answer
26 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
45 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
18 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
26 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 ...
0
votes
1answer
33 views

Mariadb - Error declaring variable in trigger

I'm trying to declare a variable to use later in my trigger, but I'm getting this error. ERROR 1064 (42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB ...
1
vote
1answer
51 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
38 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
21 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
64 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
28 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
41 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
24 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
33 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
35 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
102 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 ...
2
votes
1answer
44 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
234 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
42 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
34 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
38 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
15 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
51 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
659 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
52 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
47 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
26 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
23 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
56 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
15 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
46 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
48 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
80 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
23 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 ...