Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,177
questions
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 ...
