Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,317
questions
0
votes
1answer
19 views
How to trigger when an event crosses now()?
Is it somehow possible in postgres 12 to trigger an event when a table with an tsrange column crosses now(), or someone insert something that where the tsrange is prior to now()?
Triggers, as far i ...
1
vote
0answers
29 views
delete or rollback an insert if a trigger on insert fails
is it possible to delete or roll back insert statement if a trigger on that insert statement fails due to some reason.
for eg i have a trigger on insert into employe(id,name) values(1,'ali')
trigger
...
0
votes
0answers
14 views
PostgreSQL cascading triggers based on the condition of which other table triggered it or a value given by the other trigger?
I want to keep (slightly different) tables synced to one another and with only 2 tables, this is fine. Because of pg_trigger_depth() = 0 in the WHEN clause they will not trigger each other.
I ...
0
votes
1answer
19 views
Row Level Trigger for insert/update to a table
Given a table ITEM as below :
CREATE TABLE ITEM (
ItemNo int not null,
category varchar(20) not null,
Quantity int not null,
Price decimal(10,2) not null,
Total decimal(10,...
1
vote
3answers
36 views
Postgresql: How to pass an array to trigger function in TG_ARGV?
I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be ...
2
votes
2answers
193 views
MySQL best way to define a couple of columns as both null or both not-null
On a MySQL DB, I would like to find a way to force a couple of columns be both null or both not null at the insert / update time. This means that on each record, both columns are null or both columns ...
1
vote
1answer
66 views
How do I return all column values for the recently inserted row/rows?
I have these two tables in a PostgreSQL 12 database:
CREATE TABLE public.test (
entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);
CREATE TABLE public.test_registration (
entity_id uuid ...
1
vote
0answers
23 views
PostgreSQL: UPDATE trigger on table with OID columns doesn't fire for lo_put-based UPDATEs
I've created an UPDATE trigger on a table containing an OID column. The intent here is to copy any changes from the OID column to a related BYTEA column.
Example table:
CREATE TABLE blah01 (
id ...
1
vote
1answer
14 views
Lock All Column Changes, Except One Column On Trigger
I want to ask how to lock all updates except one column with Trigger, is it possible to achieve this?
For example my table structure is:
--------------------------
id | name | age | point
------------...
1
vote
2answers
53 views
Calculate stock value with PostgreSQL
I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my ...
0
votes
0answers
18 views
How can I auto-create a partition in a declarative partitioned table in Postgres when a newly created row has no matching partition?
My goal here is to make a partitioned table that's simplified below
Group
Info
A
blah1
B
blah2
but instead of having that as one table, I want to partition my table per group so that there's a ...
0
votes
1answer
15 views
how to compare two date column from two different tables by extracting specific parts in both columns using trigger/function in postgresql 10
I have written program something like below as per my question:
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare
username VARCHAR(40);...
1
vote
2answers
37 views
How to use tsvector_update_trigger with jsonb column?
I need to create cached ts_vector column from jsonb value. I try to create a trigger
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
...
2
votes
4answers
66 views
For every inserted row, generate series of rows in another table with AFTER INSERT trigger in PostgreSQL
Let's say that a customer places an order with certain quantity and I want to insert this information into table1 initialy. At the same time, I want to generate a series of rows in the table2 with a ...
0
votes
1answer
31 views
Postgresql - Identify process issuing a query
With this table
Table "scheduled_message"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-------...
0
votes
1answer
30 views
Perform update and raise notice in Postgres Function
I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened.
I don'...
1
vote
1answer
28 views
Delayed or Asynchronous DML Trigger Execution
I have created a DML trigger (AFTER INSERT, UPDATE, DELETE) on a table
Trigger's logic takes about 30 seconds to execute
So if you change even 1 row, it takes ~ 30 seconds due to trigger execution
...
0
votes
1answer
31 views
Create a trigger to fill a field according to acondition in sql
I want to create a trigger to fill a field according to acondition as below description
For example:
I have a table for all insps that have attachments named: “inspectionAttachments” and I have ...
1
vote
1answer
30 views
Trigger creation for Sybase SQLAnywhere 16
I've been having the hardest time trying to create a trigger for a Sybase SAP SQLAnywhere 16 database following these docs.
Currently it's -131 syntax erroring on line 15/16 (the update statement), ...
1
vote
0answers
28 views
Two inserts in a single trigger [closed]
I have this trigger (audit trigger, in every table I want to audit).
CREATE FUNCTION changelog_trigger() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT'
THEN INSERT INTO "...
0
votes
1answer
81 views
SQL Trigger to Insert a row in Table B is the record is inserted in Table A [closed]
I have a requirement where I have to Insert a Record into Table "Site" after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for ...
2
votes
1answer
43 views
Limit connection to specific database with AlwaysOn Primary
Using AlwaysOn, I have a user which previously we had granted rights on a database on the primary, disabled the login on the primary server and then created the same login on the secondary so that ...
2
votes
2answers
102 views
PostgreSQL Trigger to Update Inserted Row With Data From Another Table
I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the ...
1
vote
1answer
53 views
SQLite: Using count in triggers to update another column
UPDATE: Fixed. Not 100% sure why sqlfiddle.com didn't like my syntax, but using https://dbfiddle.uk/?rdbms=sqlite_3.27 worked. I did change the last WHERE clause to be based on the id column, so that ...
-1
votes
2answers
17 views
Create a trigger for a table whose values are dependent on another table
I have a hospital database and in that database I have an Appointments table and a Visits table. In the Appointments table I have a column named Validation that can have values 0 and 1.
I want to ...
-1
votes
1answer
23 views
Hello I want to create a before insert that looks if the value is on another table before inserting [closed]
I have a database for hospitals and I have a table for patients and one table for appointments. I need to create a trigger that if someone wants to add a person on the appointments table that person ...
1
vote
2answers
52 views
PostgresQL Delete Trigger does not delete row
I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would ...
0
votes
1answer
25 views
UTL_HTTP call inside trigger leads to ORA-06502 when inserting from select
In order to allow a developer to make a SOAP call from an Oracle 11gR1 (11.1.0.7 PSU 24) instance using UTL_HTTP.
He created a stored procedure :
CREATE OR REPLACE PROCEDURE CALL_WS (URL IN VARCHAR2, ...
0
votes
1answer
13 views
ERROR: column “FLAT” does not exit. Line 1. SELECT UPPER(FLAT). Kindly fix the trigger
CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp()
RETURNS trigger
AS
$BODY$
BEGIN
n.FLAT := UPPER(n.FLAT);
RETURN NEW;
end
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER;
CREATE TRIGGER ...
1
vote
2answers
63 views
ORDER placement for trigger in t-sql
I'm using Sybase ASE version 16.0.0.1915, (follows t-sql syntax) and trying to create a new trigger. This new trigger emulates another existing trigger, however is slightly modified. It would be a ...
0
votes
0answers
34 views
POSTGRESQL : trigger running in background with listen and notify
I am working on a PostgreSQL database containing geographical information; in my OBS_TABLE I have:
the coordinates of each point (longitude, latitude)
the value of the ocean's temperature "TEMP&...
0
votes
0answers
33 views
How to create two sqlite triggers for two tables without conflict?
Below are my example queries:
"""
CREATE TABLE IF NOT EXISTS projects (
Project_ID TEXT UNIQUE,
Initial_Project_count TEXT,
Additional_Project_count TEXT,
...
0
votes
1answer
18 views
How to compare two last updated entities from two different table Postgresql?
I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new ...
0
votes
0answers
36 views
MySQL ignore trigger errors
I have a trigger based on AFTER INSERT statement in MySQL table. Known thing that MySQL in case of any errors in trigger's statement - will cancel initial INSERT statement, called trigger, as well.
...
1
vote
1answer
40 views
MySQL 8.0.22 Trigger won't run unless definer is logged in
I have the following tigger that used to work before upgrading to MySQL 8 from MySQL 5. Now the trigger will only run if the definer is logged in via phpmyadmin or some other database admin tool. The ...
0
votes
2answers
45 views
Materialized View: What is the Update Trigger property?
I've created a materialized view in an Oracle 18c database.
When I look at the Info tab of the materialized view in Toad, I see that there is a property called Update Trigger.
What does the Update ...
1
vote
1answer
37 views
MySQL create trigger after insert not inserting all values
I am new to MySQL triggers. i want to insert the newly inserted booking from booking table to bookings_clone table.
PROBLEM:
All the field in bookings table are filled but in bookings_clone only these ...
2
votes
0answers
48 views
When is a lock acquired during a trigger's execution?
Following Situation
I'm trying to determine when a lock is acquired during the execution/firing of a trigger. I have the following setup.
Table Definition
CREATE TABLE a(
id serial PRIMARY KEY,
...
0
votes
1answer
52 views
Do triggers cause extra index updates?
I am dealing with a db managed by a 3rd party framework and I can't change it... yet. Isn't that always the case.
Long story short they are never setting nullable fields to null. They are putting in ...
0
votes
1answer
24 views
Postgresql database NOTIFICATION trigger that only returns if some sql is met
I'm trying to get a postgresql trigger that calls a notify function to work but only if the data meets certain criterion.
create trigger notify_site after insert on sites
for each row execute ...
1
vote
2answers
38 views
lock escalation issue when using DDL trigger
I have a problem, I am trying to create a log table for my database so I can keep track of changes. I have created the following trigger that runs when a table is altered, created and dropped:
CREATE ...
0
votes
1answer
324 views
SQLite: trigger for multiple tables and/or multiple actions
I am looking for a way to write SQLite trigger which will affect multiple actions and multiple tables (in fact I want it executed on a particular table each time any query is executed on any table in ...
0
votes
0answers
183 views
Can a Postgresql trigger send a row to an api via a post request
I have an application where it would be really nice to be able to use an insert trigger on a table to format and send a post request to an external API. This would save me having to write the code to ...
0
votes
1answer
20 views
how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL
Here is tbl1 :
gid pm
1 2.1
2 2.0
3 1.95
...
Here is tbl2 I want :
gid num pb gidTbl1
1 1 1
2 2 1
3 3 2.1 1
4 1 2
5 2 2
6 3 2
7 4 2....
1
vote
1answer
55 views
MS SQL Server 2019 - deadlock after row update by primary key, possibly caused by trigger
I've a table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](65) NOT NULL,
[nickname] [nvarchar](65) ...
0
votes
1answer
14 views
Trigger Definer Already Same But User Still Denied
currently I already have user like this 'app_user'@'10.148.0.0/255.255.240.0'
and I have the trigger with definer like this
CREATE DEFINER=`app_user`@`10.148.0.0/255.255.240.0` TRIGGER ...
1
vote
2answers
128 views
Unknown table 'table' in field list on referencing temporary table inside a trigger
I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS ...
3
votes
2answers
197 views
Case insensitive column names in a postgresql trigger function
I have a trigger function in PostgreSQL 12 which executes something like this:
CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
latcolumn:= (
SELECT ...
2
votes
2answers
92 views
Get current table name trigger was fired on
My system: Db2 11.1 on Linux.
1.Create some table:
create table admin.tab (a int not null primary key, b int) @
2.Create audit table:
create table admin.audit (table_name varchar(2000), datetime ...
1
vote
1answer
29 views
How do I keep a 'running total' of purchases?
Say I have an append-only table that records purchases at a store:
create table purchases(
customer_id int,
amount numeric(10, 2) -- (in dollars)
);
There's a lot of other queries that want ...
