Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,295
questions
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
54 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
25 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
27 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
16 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
16 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
38 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
37 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
31 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 ...
1
vote
0answers
42 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
51 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
20 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
34 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
81 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
24 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
48 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
44 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
83 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
74 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 ...
0
votes
0answers
18 views
MariaDB: Syntax Error on Create Trigger (with Delimeter) (but works in MySQL)
After one hour staring into my statement... I don't know how to proceed with the following statement:
delimiter //
CREATE TRIGGER `insert_transaction` BEFORE INSERT ON `transactions`
FOR EACH ROW ...
1
vote
2answers
69 views
How to use trigger to perform addition from one table columns into another table column where id matches Sqlite
Below is my SQLite Database with two tables.
import sqlite3
conn=sqlite3.connect('calc.db')
conn.execute("""CREATE TABLE IF NOT EXISTS sum
(id TEXT UNIQUE,
name TEXT,
...
0
votes
0answers
51 views
How to increase Max Concurrent with Insert trigger
Currently, I used PostgreSQL 12 with Ubuntu 16 (12 Vcpu & 24 GB of Memory)
I want to setup PostgreSQL for 450 Concurrent but Now That's maxed on 333 TPS
PostgreSQL Configuration
Max Connection = ...
0
votes
1answer
29 views
auto populate common columns of all tables using triggers
I have the following 4 tables:
course
section
student
enrollment
All of them have 4 columns in common that I want to auto-populate before an insert or update.
At first the simple insert trigger was ...
1
vote
1answer
18 views
Is it good idea to invoke Web Service from the database function or trigger?
I am using PostgreSQL in my Web API project. I have created Web APIs using .net core. Now I have a situation, where I want to process the inserted or updated data and store the processed data in some ...
0
votes
1answer
24 views
How to execute a trigger procedure with an expression as an argument?
How can I pass an expression as an argument when calling a trigger function?
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
'job_name',
(...
0
votes
1answer
34 views
Unable to delete a referenced row due to a trigger on a referencing table?
I ran into some problems in using triggers to ensure only deletions from cascading deletes:
CREATE TYPE status_type AS enum ('A');
CREATE TABLE parent(
name VARCHAR(255) PRIMARY KEY
);
CREATE ...
0
votes
0answers
31 views
Using a trigger to check if a row exists in mysql — why so slow?
I am using a trigger to check if a row exists. The code below works, but it is very very slow. I suspect that it has to do with the way I am handling NULLs.
Any suggestion to optimize it?
DELIMITER $$
...
0
votes
1answer
34 views
Use insert trigger to copy a modified NEW record to another table, with unknown columns
I use a Postgres extension (promscale) that dynamically creates tables with similar schemas: the tables all have a 'time' and a 'value' columns, then a number of labels columns.
Upon insert in any of ...
-2
votes
2answers
52 views
How to prevent database shrinking
We have a 3° party application, with an application user who needs to be db_owner on the database (not my choice) on SQL Server system. All stored procedures are encrypted in this database. At some ...
0
votes
2answers
74 views
How to delete records with FK constraint using Delete Trigger
I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my ...
0
votes
0answers
7 views
Before deleteion trigger for self joined table sub rows
I want to write trigger before deletion to delete sub-children from same mysql table.
My table is samp_c:
**ID** **Name** **key** **Sub-key**
1 sample1 ab 0
2 Sample2 bc ab
...
0
votes
1answer
40 views
How To Tell if Trigger is failing
I have a trigger, that I want to try to update a field based off the value of another table. I have manually verified that the data exists in the other table so I know the join will work. However, ...
0
votes
1answer
48 views
“SQL command not properly ended” with trigger
Problem
No matter what I do, I can not get my trigger to work. It seems to always complain about SQL command not properly ended, ignoring SQL statement and so on.
Research
I have spent hours looking ...
0
votes
2answers
44 views
Trigger: Only raise error if related record was NOT created between now and last Sunday
I have a trigger that prevents inserts into a DELETES table under certain conditions:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE
v_upd_row NUMBER;
...
2
votes
1answer
105 views
What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?
My company logs DDL on one database using a Database Trigger, looking for DDL including:
ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
When I disable or enable triggers using ALTER TABLE x ...
0
votes
0answers
30 views
Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts
I have a simple table with a geometry column like so:
CREATE TABLE geomtable
(
gid serial,
geom geometry,
trip_count integer
);
I want a unique constraint on the geometry column, such that ...
1
vote
2answers
40 views
Using NEW on tables with generated columns
I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially ...
0
votes
0answers
42 views
Best way to log all changes at column-level for every table in a database?
I have a database consisting of several tables and in a separate table, I would like to store every column within every table and the last time the data in the columns was modified (i.e. following an ...
1
vote
2answers
36 views
SQL Server cluster node fail over details
I would like to know the SQL Server cluster node fail over details immediately as soon as the passive node is active as I have some dependency on active node. For that, I see sys.dm_os_cluster_nodes ...
0
votes
0answers
19 views
Mysql falls every day
My database Mysql fallas every day:
Looking at the tail -100 /var/log/mysqld.log
Shows:
2020-08-16 20:56:03 7f787c7fb700 InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
...
1
vote
1answer
59 views
How do I refer to tables in a trigger? / OR / How do I look up FK values to be added on insert automatically?
My goal is to have Oracle fill in FK values that correspond to the right PK values of a parent table.
I have this trigger:
CREATE OR REPLACE TRIGGER connect_purified_enz_to_produced
BEFORE INSERT ...
0
votes
0answers
51 views
Making a trigger to update FK based on columns in another table and an external table
I'm inserting data into a database and I want the FKs to be populated automatically based on other information that links each FK to its corresponding PK.
Here's my plan:
Create an external table of ...
2
votes
2answers
75 views
SQL Server Unique Constraint on two columns with an exception
Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns. However, if DocumentID = 'NA', I would like ...
0
votes
0answers
17 views
PostgreSQL rule to delete record before insertion, if the record has changed
I'm not sure it's possible to do this with a rule, but what I want to do is, when you insert a record:
If it doesn't exist, just insert it as usual.
If it exists, but each field is equal to what you'...
0
votes
0answers
21 views
Trigger having issues on partioned table execution - Postgres - 11
Postgres - Trigger having issues on partitioned table execution
I am using postgres 11 version
I am trying to execute a trigger on partitioned table with customized function and trigger function,
that ...
0
votes
2answers
112 views
Oracle 12c - Insufficient Privileges When Trying To Create A Trigger With SYS User
I want to create a trigger for CRE schema. I'm trying to create this trigger with SYS user but I have insufficient privileges error.
My trigger:
CREATE OR REPLACE TRIGGER CRE.TRIGGER_NAME
AFTER LOGON ...
0
votes
0answers
100 views
Postgres, trigger function, is it bad for performance?
I have added the following trigger function to one of my tables in my Postgres database.
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
...
