Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,343
questions
0
votes
0answers
55 views
Unable to get FileStream Length with After Insert trigger
I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I'm populating ...
0
votes
0answers
19 views
How to run both "before" and "after" update in trigger
I need to calculate prior values in a table before the column values are updated. Basically, I have to calculate the difference between the before the update and after update values. If there is any ...
0
votes
0answers
6 views
After insert trigger is not working on Aurora Writer instance
I have an After insert trigger on aurora writer instance and it is not inserting any records into the audit log table after the record is inserted in the table.
This is my AFTER INSERT trigger ...
0
votes
1answer
50 views
ERROR: syntax error at or near "END" [closed]
I am trying to play around with trigger and found some samples online which I pretty much copied before except changed table name and row counts. But somehow I kept on getting syntax error and ...
0
votes
1answer
42 views
Use trigger on DELETE to retrieve original SQL command and user who did it
I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012.
What I need is the username that executed it and the command executed, then I can insert them on a log ...
0
votes
1answer
24 views
Postgresql - Is calling a stored procedure in a trigger possible?
I would like to call a stored procedure using a trigger in Postgresql. Is it possible?
Here is an example:
create table foo(n int primary key, n1 int);
create or replace procedure ...
1
vote
1answer
29 views
Is it possible an AFTER DELETE per-row trigger is not run?
I've created a function which inserts in an audit table some data about the row that just has been deleted, and created an AFTER DELETE per-row trigger on the table I want to audit.
This is working ...
0
votes
1answer
21 views
Postgres preventing update on "events" table but insert "warning" event
I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database.
My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
1
vote
1answer
20 views
Calling a boolean function for Trigger WHEN conditions
The following,
CREATE TRIGGER _trigger_insert_form_metadata
AFTER INSERT
ON my_table
FOR EACH ROW
WHEN _conditions_form_metadata(NEW)
EXECUTE PROCEDURE _trigger_insert_form_metadata_function();
It ...
1
vote
2answers
61 views
Error: Dynamic SQL is not allowed in stored function or trigger
Im trying to convert multivalued column in Table A into rows of Table B(The data type of the column is varchar).
For ex:
Table A
id value
ae [1,2,4,5]
ac [1,6]
Table B
id value
ae 1
ae 2
ae ...
0
votes
0answers
15 views
Postgres: Triggers Not Created for New Partitions
I have a table that is partitioned into a hierarchy of partitions as follows
A -> A_x -> A_x_y -> A_x_y_z
Where x is the project id, y is the instance id and z is the month.
I've created an ...
1
vote
1answer
45 views
Column constraint based on values in another column
I have a PostgreSQL table phase_steps, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| ...
0
votes
0answers
27 views
Incorrect number of likes and dislikes while aggregating from trigger
I have a table in PostgreSQL
feed_item_likes_dislikes(feed_item_id, user_id, vote)
where
feed_item_id is UUID
user_id is an integer
vote = TRUE then it is alike
vote = FALSE then it is a dislike
...
0
votes
0answers
42 views
Postgres: Event trigger for logging all dbs changes to one table
I am trying to create an generic event trigger, which I set up in every future databases, I create. This trigger should update the main db's table every time one of the other databases have add or ...
0
votes
1answer
28 views
Postgres per-statement update trigger relationship between old and new transition tables
Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs
My use-case is that I have a "shadow" table ...
0
votes
1answer
74 views
Oracle: "table is mutating, trigger/function may not see it"
(Yes, this is one of those question again...)
I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server.
I have a table I1 (called t_link in my ...
-1
votes
3answers
73 views
after insert trigger not firing after inserting with bcp
content of csv file
TEST_1|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 5528, 'event_string': 'ENERGY_CTRL_RIGHT_PROX_DOWN', '...
0
votes
1answer
26 views
Trigger updatedAt=NOW() for hunded-thousand rows will not actual [duplicate]
I am using SQL Trigger to update updatedAt column if there is a new data inserted/updated in PG 9.6.
Here is the function that will be called by trigger
CREATE FUNCTION sp_updatedatstamp() RETURNS ...
3
votes
1answer
194 views
Disabling triggers makes DELETE fast, but the table has no triggers?
I am deleting 130k out of 750k rows from a PostgreSQL table.
The first time, it took 8 hours to complete the delete query.
Second time, I added an index to the table, and rebuilt that index. Now it ...
0
votes
0answers
87 views
Database users are lost after restore - how to fix
We had many facilities that operate in areas where there is poor/no internet connection; the facility staff sent us monthly a backup of the facility database for us to restore it on our data center.
...
2
votes
2answers
125 views
Enforce that every company has at least one CEO
I've a requirement to ensure that every company has at least one CEO.
Example database model with requirements db<>fiddle.
Basically, a CEO can't be removed from a company without naming another ...
5
votes
3answers
168 views
Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many
We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision ...
1
vote
0answers
18 views
Update a PostgreSQL row with last user and timestamp
I would like to track who is the last user who updated the rows of a PostgreSQL table (v9) and when. This webpage explains how to do it with a trigger regarding the timestamp (I did not try it yet), I ...
-1
votes
1answer
39 views
how to get the email address from inserted data as the recipient in sp_send_dbmail
I would like to use the email address that is inserted in a column as the @recipient in sp_send_dbmail. This code is a trigger that I've been trying to achieve this with and is for test purposes only:
...
1
vote
1answer
336 views
Create a Hangfire job via SQL stored procedure
I know how to create Hangfire jobs via .NET C# code, but there are a few occasions where I'd like for a database trigger to execute a Hangfire job. I searched the web but found nothing so I attempted ...
2
votes
1answer
101 views
SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)
I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a ...
0
votes
1answer
85 views
Stored procedure to change string to datetime in MySQL
This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my ...
-1
votes
1answer
29 views
Why PG 13 does not allow deferring statement trigger constraints?
I noticed in PG 13 docs that only row constraint triggers can be deferrable.
Why not statement constraint triggers?
0
votes
0answers
33 views
How can I link together auditing of the sql and before and after values for row updates in Oracle
I had a number of triggers that used to work in Oracle 8i, that worked to capture both the before, and after values of a row, as well as the sql used to do the update.
(I realize there would be cases ...
0
votes
1answer
37 views
Insert into table if column <> "Text" for each project
I have created two triggers to insert into fact table if a new row is inserted into dim table. One checks if Project exists already and if not, then query will be triggered and adds 12 new rows per ...
0
votes
1answer
50 views
Using Trigger creates duplicates
I have created a trigger to inert into table2 if a new row is inserted into table1 (at least I thought).
The queries is like this:
CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert ...
-1
votes
2answers
58 views
Insert new rows when item is added to another table SQL [closed]
I have to tables. One dimension table with projects and one fact table with projects, value and a period(month).
Looking to add 12 new rows pr project ID into my fact table, when a new project is ...
0
votes
1answer
38 views
Cascading PostgreSQL triggers between three tables
I have a ‘test’ PostgreSQL 12 schema, defined with initial tables:
begin;
drop schema if exists test cascade;
create schema test;
set search_path = test,public;
create table jobs (
job_id serial ...
0
votes
1answer
35 views
MS SQL Creating Tables and Adding Values by a Trigger
Table_A is an existing table which has 3 columns Id (PK), Name, Stock.
I would like to create a trigger to create a table.
For example, Table_A has an item, Id (PK) = 3, Name = Apple, Stock = 10.
By ...
1
vote
1answer
39 views
Thousands of After Insert and Update Triggers Causing Performance Issues
We have a After Insert and Update trigger on one of our tables. The trigger basically makes a json payload and enqueues it in a RabbitMQ system.
Today a large insert script was run on the table (over ...
0
votes
3answers
86 views
Replicating contained SQL databases to customer in near real-time
We host a number of customer SQL Server databases that we need to replicate to external customer sites in near real-time. The main constraint we have is that, for security reasons, our databases are ...
1
vote
1answer
30 views
Junction table Trigger after update in another table
I'm just trying to create a trigger that performs an update in a junction table.
In principle, I have three tables with Many-to-Many relationship.
Table a:
a_id, a_is_active
Table b:
b_id, b_is_active
...
0
votes
1answer
27 views
How to trigger an event when row column 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
70 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
37 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
29 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
393 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
329 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
120 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
33 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
26 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
248 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
840 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
16 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);...
2
votes
2answers
165 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
...