The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

214 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
3k views

ORA-04091: table ExpenseTable is mutating, trigger/function may not see it

I have two tables and a trigger and am inserting values in ProjectsTable like this: CREATE TABLE ProjectsTable ( ProjectID NUMBER(6) NOT NULL, ProjectName VARCHAR2(200) NOT NULL, Cost ...
5
votes
1answer
329 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
0answers
1k 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
0answers
1k 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 ...
3
votes
2answers
133 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" "...
3
votes
1answer
5k views

mysql trigger update after insert

I need help to create a trigger that can calculate prices after promotions. I have two tables "product" and "promotion". after inserting in promotion table the promotion price and the product id, I ...
3
votes
0answers
542 views

Pass additional data in connection for use by PGSQL Trigger

I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started. What I would ...
3
votes
0answers
2k 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 ...
3
votes
0answers
487 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 ...
3
votes
0answers
110 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. ...
3
votes
2answers
1k views

Java Spring application Database triggers auditing - how to provide the right user that made the change

We are developing a new web application. one of the most basic requirement is to audit all entities changes into a separate table. We would like to use DB triggers for that purpose. We use MySQL as ...
2
votes
0answers
45 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 ...
2
votes
1answer
50 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
2answers
196 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 ...
2
votes
0answers
46 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
0answers
28 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? ...
2
votes
1answer
196 views

Setting up a cascading trigger on a parent table

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards: Table "postcards" Column | ...
2
votes
0answers
400 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
0answers
1k views

refresh a materialized view after update of tables

I am currently stuck in postgres at creating an updateable MATERIALIZED VIEW. The MATERIALIZED VIEW consists basically from 2 (foreign) tables. This is an example for the tables: id | data ---+----...
2
votes
0answers
724 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 (...
2
votes
0answers
930 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
0answers
2k 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 ...
2
votes
0answers
184 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 ...
2
votes
0answers
138 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
0answers
671 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
0answers
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
0answers
1k 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
0answers
1k 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 ...
2
votes
0answers
187 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()=...
2
votes
0answers
74 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
0answers
137 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 ...
1
vote
0answers
20 views

MySQL Multiple IF statements within after insert trigger fails

I have been working on this for about 2 days and am at my wits end. SOS StackExchange Gurus! Can anybody help me PLEASE?! Okay, so I've got the following two triggers set up - one works the other ...
1
vote
0answers
28 views

How to switch off statement logging from a trigger in PostgreSQL

I just spent ages working out how to record messages from triggers into a log file. And then nearly as long working out where the logs files were being created. Anyway, I added this line to my ...
1
vote
0answers
34 views

Is it possible to setup a CREATE_LOGON trigger that treats AD Groups differently from users?

Just started in a DBA role with a new organization and they gave me a bit of a head-scratcher. We've got a trigger on CREATE_LOGON that DENYs VIEW ANY DEFINITION & VIEW ANY DATABASE. This was ...
1
vote
0answers
69 views

Start dbms_services on standby database

I'm developing a trigger which starts and stops services when the pdb is open. The point is that my trigger doesn't stop the service if I execute the ALTER PLUGGABLE DATABASE ALL SAVE STATE; with ...
1
vote
0answers
63 views

SQL Server 2017 Merge Not Running Triggers

Everywhere I am reading, when a merge statement happens, it will fire the triggers (INSERT, UPDATE, DELETE) in no particular order. I have a basic merge: MERGE dbo.Users WITH(HOLDLOCK) as tgt USING #...
1
vote
1answer
52 views

Mimicking cascading foreign key referencing system table

I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables. On INSERT it ...
1
vote
0answers
14 views

Only allowing selected values of different linked tables be selected. MySQL Trigger

I have 3 tables. I have an instructors table and facilities table. They are linked by facility.facility_id=instructors.facility_id (i.e. A facility can have many instructors but an instructor can ...
1
vote
1answer
99 views

MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin

I have the following two Triggers in my MySQL DB that fire BEFORE/UPDATE and AFTER/UPDATE on the house_report_details table. The first deletes all records in my scratch table tmp_house_rpt_details and ...
1
vote
1answer
24 views

Open encrypted trigger of table in database (MS SQL Server Management Studio)

Microsoft SQL Server Question for Trigger in database-tables. Is there a possibility to view Code behind a encrypted trigger of a database table? I only see a key inside the Icon and rightclick "...
1
vote
0answers
53 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 ...
1
vote
0answers
357 views

Trigger for add discount on total price calculated from other table - oracle 11g

I have the following schema: CREATE TABLE ORDER( OrderId NUMBER PRIMARY KEY, Value NUMBER); CREATE TABLE ORDERITEM( OrderItemId NUMBER PRIMARY KEY, OrderId NUMBER, ...
1
vote
1answer
385 views

Deleting old rows when a new one is added and there are more than 100 rows

For a school assignment we need to keep the number of rows in a mysqldb under 100. The newest rows must stay. I've tried using a trigger but with no success. CREATE TRIGGER delete_rows AFTER INSERT ...
1
vote
2answers
196 views

Trigger - If time between?

I wish to use a mysql trigger that will update a column called peak to 0 or 1 based on if the CURTIME() is between 08:01 & 11:59. Can anyone help with this trigger?
1
vote
0answers
33 views

How to aggregate transactions to form a stateful view which can be updated efficiently?

How to aggregate transactions to form a stateful view which can be updated efficiently? For example how to turn the following transactions: CREATE TABLE txns_combined(from_party,to_party,price,qty,...
1
vote
1answer
62 views

Notification of row entries

I have SQL Server 2012 machine housing a database running an off the shelf application with a users table. This users table contains PII enterable fields. Per requirement none of our databases can ...
1
vote
0answers
112 views

Entry inside audit log table for CUD operations on tables

I have a DB e.g "dbAuditLogs" inside created tables like as mstUser,mstEmployee,mstDepartment and auditHistory. when any CUD operation performed on mstUser/mstEmployee/mstDepartment then want to ...
1
vote
0answers
34 views

problem using count in trigger phpmyadmin

how to record the number of column lastbuy_idrbch and lastsell_idrbch from table idrbch in my new table (test) using trigger and count function ?
1
vote
3answers
157 views

Trigger INSERTING into a row

I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one. CREATE TABLE ...
1
vote
0answers
54 views

data synchronization between two dbs

I need your help on some replication matter I guess. I'm actually not a dba, but I need to do some work on my db: I have a remote db, with table1, table2 and a local db with table3 and table4 in local ...