Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,189
questions
0
votes
1answer
25 views
Is it possible to pass a built in function as an argument to a trigger?
I ask this question in light of the following trigger producing this error:
ERROR: syntax error at or near "("
LINE 5: ...cessBlogPostApproval"('Blog Post Approval', concat('Your blo...
^
The ...
0
votes
0answers
22 views
PostgreSQL call a function in a function
I have 2 tables. One trigger on each.
On tbl2 : ON INSERT, trg2 takes a value from a column named "z" in tbl1 and do some maths (in tbl2).
On tbl1 : ON UPDATE, if a value of "z" has changed, I would ...
0
votes
1answer
22 views
PostgreSQL - triggers to fire only on replicated db
Is it possible to fire triggers only on a read-only Postgres replicated database?
I want triggers to fire only on the replicated database - not on the master (to avoid load). This is to track - say - ...
0
votes
1answer
23 views
Accessing old version of referenced table in trigger function - Postgresql
In my database I have a table named "layer" and a table named "grouplayer". The table "layer" is referencing the table "grouplayer" by a foreign key constraint with Cascade DELETE.
I have added a ...
0
votes
0answers
10 views
Running a series of Scripts, one by one, a soon as Last DB entry is populated by cron job? [closed]
I have a PHP cron job that is creating a fresh DB table. the process takes somewhere between 20 to 30 minutes.
I also have a PHP script that should run once all DB data was fetched, and its role is ...
0
votes
0answers
15 views
Create postgresql function to be triggered before insert row
I've created a function to be executed before inserting rows in a table, in order to avoid the error "malformed array literal". It still throws the error and I deduct that the process starts earlier ...
0
votes
1answer
28 views
Can a trigger with an “insert into” statement cause Intext Exclusive locks for small insert operations?
We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as ...
0
votes
1answer
25 views
Trigger function to update data from one table to another table with different servers in PostgreSQL
There are two databases with different servers in PostgreSQL in PGAdmin.
One database db1 of server s1 and another database db2 of server s2.
The condition is: if the field id.table1 of db1 is same as ...
0
votes
0answers
29 views
How trigger update in replica database on Postgres?
In both servers (master and replica) I have two tables like this:
CREATE TABLE my_table
(
id SERIAL,
name TEXT,
"timestamp" timestamp without time zone,
CONSTRAINT my_table_pkey ...
1
vote
1answer
23 views
Postgres 12: declare a function as constant or immutable ignoring parameters?
In a trigger function I want to conditionally do pg_notify() calls, depending on an application setup option. In order to do that I created a wrapper notify_conditionally(). If the application is ...
-1
votes
0answers
39 views
I have two triggers, after insert and after update, they are populating 'null' rows in the trigger table
I have two triggers, after insert and after update, they are populating 'null' rows in the trigger table
My Trigger Code is
USE [KFC_Japan_Utility]
GO
/****** Object: Trigger [dbo].[trgAfterInsert]...
0
votes
2answers
22 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
41 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
1answer
21 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
30 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
32 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
13 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
52 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
41 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
19 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
47 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
34 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
46 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
48 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
1answer
46 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
votes
1answer
29 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
55 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
27 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
43 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
58 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
39 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
24 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
65 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
29 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
42 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
27 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
39 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
138 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
45 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
298 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
46 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
36 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
41 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
18 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
56 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 ...
