Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

270 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5 votes
1 answer
935 views

Server Logon Trigger fails after deleting many records in "Logon-Table" -SQL Error 2801

We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table. The table doesn't have clustered index, but it does have a "PK_ID"...
4 votes
0 answers
128 views

Does specifying STRICT on a PostgreSQL trigger procedure do anything?

Does specifying CALLED ON NULL INPUT, RETURNS NULL ON NULL INPUT or STRICT (https://www.postgresql.org/docs/current/static/sql-createfunction.html) on a trigger procedure in PostgreSQL do anything? ...
  • 221
4 votes
0 answers
1k views

Postgres: Recreate TRIGGER after replacing function?

Given a function like this: CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ BEGIN -- do something fancy END; $$ LANGUAGE plpgsql; which is used in a TRIGGER: CREATE TRIGGER bar ...
  • 395
4 votes
0 answers
2k views

Preventing Infinite Trigger Loops in PostgreSQL

I am working with two PostgreSQL databases (one internal and one on our company Web server) where I am attempting to share data between the two instances through the use of triggers. The databases ...
4 votes
0 answers
119 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
  • 99
4 votes
0 answers
2k views

Ways to process XML into a relational structure in Postgres

I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw ...
  • 141
3 votes
1 answer
582 views

check if value exist in another table before insert SQL

I have table: " daysoff " and table: " rdv " the rdv table looks like this : temps_rdv etat_rdv ID_RDV 12-10-2022 reservee 2 the daysoff table looks like this : ID DATE_OFF ...
  • 41
3 votes
0 answers
714 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, ...
  • 81
3 votes
0 answers
45 views

Is it possible to log whenever someone decrypts a credit card in MSSQL 2012?

All of our credit cards are encrypted. The process to decrypt them is to Open the symmetric key and decrypt the data by certificate. Is it possible to add a trigger on something in the process to ...
  • 133
3 votes
2 answers
399 views

Do MySQL (5.6) triggers support 4 byte emojis?

I'm using MySQL 5.6.34 with the following settings: "character_set_client","utf8mb4" "character_set_connection","utf8mb4" "character_set_database","utf8mb4" "character_set_filesystem","utf8mb4" "...
  • 31
3 votes
0 answers
1k views

execute R script using a postgresql trigger:

This is my first question here, so excuse me if it does not look well organized: I want to call a R script from a trigger (postgresql) so this script will be executed after an INSERT on my table (...
  • 31
3 votes
1 answer
5k views

Postgres: How to log materialized view refresh

I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it ...
3 votes
0 answers
3k views

Which is the right way to fix "Error: 1205 Lock wait timeout exceeded; try restarting transaction"

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
  • 1,688
3 votes
0 answers
772 views

Trigger failing when called using sp_executesql

We have a recursive trigger on a table that executes AFTER INSERT. There is a check to ensure the trigger does not recurse more than 32 levels deep: IF(SELECT TRIGGER_NESTLEVEL()) >= 31 ...
  • 131
3 votes
0 answers
2k views

How to avoid locking problems while disabling/enabling triggers in a PostgreSQL function?

Whenever I try to disable a trigger from within a function, I get following error: ERROR: cannot ALTER TABLE "datace" because it is being used by active queries in this session It seems logical ...
  • 737
3 votes
0 answers
216 views

logon trigger to send an email

I want to create a simple login trigger. basically if user = test , then send an email to me. CREATE TRIGGER ServerLogon ON ALL SERVER WITH EXECUTE AS 'test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()=...
3 votes
4 answers
4k views

I don't know how to fix ORA-04091 mutating trigger error

I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table. The error is: error ORA-04091: table USUARIO.LINEAS is mutating, ...
2 votes
1 answer
111 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 ------------...
2 votes
0 answers
154 views

data-modifying CTEs and triggers

If a data-modifying CTE inserts into two tables t1 and t2 that both have triggers, is there any guarantee on the order in which statement-level triggers are run? -- t1 and t2 both have insert ...
2 votes
0 answers
204 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 ...
  • 877
2 votes
0 answers
248 views

Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction

Is there a way to catch a QUOTED_IDENTIFIER error without failing the transaction? I have assembled a minimal test case here. The trigger must have SET QUOTED_IDENTIFIER ON because it uses XML data ...
  • 6,592
2 votes
1 answer
124 views

Logon Trigger Unexpected Result

I have only server logon trigger and it is being used to prevent logins coming from an application/server combination. It works appropriately for that application/server combination and gives the ...
2 votes
0 answers
403 views

Azure Database for PostgreSQL and Event Triggers

I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as ...
  • 121
2 votes
1 answer
1k views

Trigger on 2 tables : issue with INSERT and UPDATE

This is related to my previous question: Trigger to cascade updates to a second table. In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields ...
  • 197
2 votes
0 answers
83 views

Is there a setting in SQL Server to keep triggers from firing SELECT @@IDENTITY

This is a SQL Server 2014 Server and we have application code the does a Select @@Scope_Identity after an insert into a table. Recently other developers added triggers to this table and ever since, ...
2 votes
0 answers
612 views

Auto-update hierarchyid of multiple records in INSTEAD OF UPDATE trigger

I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run ...
2 votes
0 answers
2k views

PostgreSQL: Deadlock on insert trigger that also updates same table.

I am trying to implement a type 2 slowly changing dimension in a PG data warehouse using a trigger function. The basic idea is that when a new row is inserted, we should check whether NEW is a ...
2 votes
1 answer
980 views

Force replication to statement for INSERT to a table to fire trigger on slave

We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that ...
2 votes
0 answers
243 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
  • 121
2 votes
0 answers
412 views

Migrating schema from Entity-Attribute-Value (EAV) model to proper tables

I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible. Existing ...
  • 131
2 votes
0 answers
157 views

Options for copying data out of continuously updating tables

I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database. It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database. It's used for business ...
2 votes
0 answers
782 views

Mysql Trigger use inserted value as column name for update After Insert

I have 2 tables: One for storing order_products and another one for keeping inventory levels for multiple stores The order_products table keeps the ID for the store in the store_id column. ...
2 votes
0 answers
1k views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
2 votes
0 answers
2k views

Postgresql trigger - get all changed rows after transaction

I have invoice_item table and invoice table. I need to check total price of Order after each transaction. Since I still want to be able to change things, I need trigger after each transaction. CREATE ...
2 votes
0 answers
82 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
2 votes
0 answers
180 views

SQL Server and updating windows firewall rules

Any ideas how should I proceed if I would like to update the firewall rules (essentially add ip-s to existing firewall rule). The step of exporting ip-s to text file and then using command line to ...
  • 439
2 votes
2 answers
388 views

Deadlocks With Trigger Based Replication

A table ORIGINAL exists with the following structure: ID VARCHAR Length 10 (key) VALUE VARCHAR Length 10 (non-key) A table REPLICATION exists with the following structure: ID ...
  • 121
1 vote
0 answers
25 views

Is there a way to aggregate PostgreSQL trigger's "FOR EACH STATEMENT"

In the documentation of PostgreSQL's triggers Example 43.7. Auditing with Transition Tables CREATE TRIGGER emp_audit_ins AFTER INSERT ON emp REFERENCING NEW TABLE AS new_table FOR EACH ...
  • 111
1 vote
0 answers
36 views

update trigger after insert on same table

So, I'm supposed to make a trigger that applies md5 to a row after an INSERT was made on it. Here is what I came up with: delimiter $$\ create trigger pd_encrip before insert on usuario\ for each row\ ...
1 vote
1 answer
74 views

how to prevent out of shared memory error when refreshing materialized views

Postgresql14, Windows server 2012 I get an out of shared memory related to a insufficient max_locks_per_transaction. I have two tables: tbl1 has insert/update every night. These actions trigger a ...
  • 197
1 vote
0 answers
64 views

How do I test the estimated query plan of a portion of a trigger that uses inserted/deleted?

I'm performance tuning a SQL Server trigger that has several statements using the "inserted" and "deleted" tables. How can I break out those individual queries from the trigger ...
  • 301
1 vote
0 answers
79 views

How to keep up-to-date sums in the tree?

I have a tree table. I need to ensure that when inserting or updating a record, the sumvalue field of all parents is updated - this is the sum of all child elements. I wrote a trigger (even a query is ...
  • 119
1 vote
1 answer
135 views

Oracle PL/SQL: Trigger Syntax

I'm kinda new(like 1 year of experience) in PL/SQL and i have to make a kind of tricky trigger, and i want to check if my syntax is correct, mainly the selects, inserts and variable value store i make ...
1 vote
0 answers
28 views

Clever upserting and deleting

Let's say I have a table that has roughly the following schema (for brevity, I only kept the relevant fields): CREATE TABLE IF NOT EXISTS some_table ( id SERIAL NOT ...
1 vote
1 answer
206 views

PostgreSQL - Recursive Foreign Key - Depth of 2 Check

In my system, I have employees that belong to a single department. However, some departments are large enough to have a sub-departments, so I have created a single department table like so: CREATE ...
1 vote
1 answer
1k views

Use variable as column name

I'm trying to create trigger in PostgreSQL 14: create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$ declare equipment_id int := new....
  • 11
1 vote
0 answers
151 views

Prevent Dacpac dropping columns via ALTER_TABLE trigger in TSQL

We're using SSDT to deploy database updates to clients, but sadly they will soon have the ability to add their own columns to tables in their databases. Obviously these won't be included in our model, ...
  • 11
1 vote
0 answers
1k views

Postgresql trigger insert on another table after update and update other tables

I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger. I have an intermediate table deleted_post where I insert the ...
1 vote
1 answer
505 views

Triggers: counting rows in a bridge table after INSERT

I'm using a trigger in order to make a Statistic Table. I have this many-to-many relationship where I have an Order that can have multiple Product's and viceversa. So after an INSERT on Order, using ...
1 vote
1 answer
884 views

MariaDB error with trigger and end statement

I am getting a MariaDB server version for the right syntax to use near 'END IF; END' I've tried to remove the semi colons sitting getting the errors, I thought I had done it in the correct syntax. Can ...

1
2 3 4 5 6