Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

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

1
2 3 4 5
26