Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
27