Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,231
questions
0
votes
1answer
23 views
Is it good practice to only delete through a trigger to enforce 'deleted by' auditing?
We've decided to design our database such that we actually delete rows when we want to delete some data, rather than just marking them as deleted, because the latter approach means that you always ...
0
votes
1answer
23 views
How to update data with a trigger function and a join
I have two relations :
CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int);
CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int);
INSERT INTO ...
0
votes
1answer
27 views
MySQL : Stored Procedure from Trigger giving duplicate results
I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK.
When i'm ...
0
votes
0answers
30 views
Oracle Apex Shoot email when approvers approve an issue using triggers and apex mail [migrated]
I am developing oracle apex app where currently when issue is raised it sends email to all approvers who are Approver 1 in the particular issue dept.
After this, only when both approvers select ...
0
votes
0answers
36 views
Trigger to loop and send incremental email based on condition [duplicate]
I have a requirement in Oracle apex to implement dynamic approval workflow in issue tracker.
I am trying to write a trigger for the same as below. I need to further modify it so that it runs ...
0
votes
1answer
28 views
only last row is inserting in table when having “instead of insert” trigger on that table [duplicate]
I have this trigger
CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test]
instead of insert
AS
begin
declare @name nvarchar (50),@city nvarchar (11),
@tdate datetime , @fid int,@fopd datetime;
select @...
0
votes
0answers
34 views
PostgreSQL logical replication for async service communication? [closed]
Has anyone attempted to use logical replication with table partitioning for async service communication?
Proof of concept (with images): https://gist.github.com/shuber/...
0
votes
0answers
14 views
Create mysql update trigger where another table
i have vehicule table with kilometres column and status .
And mp table with km column
I want to create trigger that updates the status based on the km column from mp table.
If vehicule.kilometres ...
0
votes
1answer
30 views
Are having TRIGGERs for every upvote/downvote detrimental in terms of performance?
I plan to have three tables: Users, User_Reputation and Voting_Records. The Users table would hold the primary key id for the registered users, along with personal information such as email and ...
0
votes
1answer
23 views
How to pass a newly INSERTed row to a function?
I have following tables:
table1: table2:
| uid | did | | uid | did |
|-----+-----| |-----+-----|
| 1 | 2 | | '1' | 2 |
| 4 | 2 | | '4' | 2 |
| 2 | 3 | | '2' | 3 |
| 1 | 8 ...
0
votes
0answers
13 views
PostgreSQL - problem with INSERT trigger enabled when performing COPY FROM STDIN bulk insert (hangs/locks)
So I work with a database that regularly imports a significantly amount of records- on the orders of millions. To do this the most efficient way possible, I use the PostgreSQL COPY FROM method, which ...
0
votes
1answer
32 views
Logon failed for login due to trigger execution / LOGON TRIGGER
I have this situation.
I created a logon script to block any session for specific logins. These logins are in a table.
So the behaviour of my script is simple.
Get the login from the table, compare it ...
0
votes
2answers
21 views
Can we prevent table from update directly but allow update using trigger from another table?
So, let's say I have 3 tables:
stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty
What I want to do:...
2
votes
1answer
37 views
Can I update old data and insert new data if not exists currently in a single query
I was trying to wrap my head around how to go about doing an update to a table if an updated list of the same table type does not have one or more of its original rows. The list of services comes from ...
0
votes
0answers
38 views
Cascading Triggers
I'm running MariaDB 10.2.31 on Ubuntu 18.04.4 LTS. My data consists 99% of JSON-encoded strings from a remote API.
Example (orders) very much simplified:
{
"id":123,
"referrer": "webshop",
...
1
vote
1answer
52 views
Set var=“NEW.this_word_column” in a trigger function if “word” is present in a column name
Context
I'm using PostgreSQL 10.12.
I want to be able to fire a trigger function on some specific tables with geographic data.
This trigger function is designed to create a PotGIS point into a ...
0
votes
1answer
50 views
PostGIS trigger is fired without error, but the action seems not executed
Context
I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points.
This trigger must compute a new geometry for each inserted or updated point. This new geometry must be ...
0
votes
2answers
40 views
Dynamic auditting trigger without temporary tables
We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows. In order to use the INSERTED and DELETED tables in ...
0
votes
1answer
17 views
update all rows after insertion in mysql
I am working with mysql and I have a table with the following structure (a summary):
CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`...
0
votes
0answers
20 views
Sql service broker and SqlDependency
I have a system where my TCP/IP command prompt application receive data and insert that data in [database1].[mytable].
Now I want to move that data from [database1].[mytable] to [database2].[...
0
votes
0answers
11 views
cannot create trigger without super privilege even though bin_log is off
I'm trying to import a magento 2 database dump to a MariaDB 10.1 server however it's asking me to provide SUPER privilege in order to finish the import.
It fails with the message
ERROR 1227 (42000) ...
0
votes
0answers
12 views
plpgsql : tg_table_name don't exist? [closed]
I've written this function
CREATE OR REPLACE FUNCTION nonull(param text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
tbl TEXT := quote_ident(TG_TABLE_SCHEMA) || '.' || ...
0
votes
0answers
22 views
MySQL - Check out trigger not executing
Im implementing a trigger to be executed when the end date of a booking is passed
Its supposed to change a cats status within checked in from 1 to 0
when updating the table changing an ex booking ...
0
votes
1answer
30 views
Slow Trigger performance when big batches (take2) [duplicate]
take2 of this post since it was missing information and it was closed prior to the update that I added.
I have an update trigger that inserts into auditing tables. We had no problem until someone ...
0
votes
1answer
36 views
Why doesn't my trigger put data into my table?
When I execute my trigger, it says it executed commands, but there is still no data in the tAttackPlan where it is supposed to insert data into. Can not figure out why.
SET ANSI_NULLS ON
GO
SET ...
0
votes
1answer
19 views
use two condition in a trigger [closed]
I have 3 tables (shipment, Supplier, Part).
create table Shipment (
SID int,
PID int,
Qty int,
);
create table Supplier (
SID int,
Sname nvarchar(10),
Status int,
);
...
0
votes
0answers
16 views
Mysql replication trigger error
I have replication master - slave on two different machines (both centos 7, mariadb 5.5).
When on master i run query which fires some trigger i've got error in Master's logs.
[Warning] Unsafe ...
0
votes
1answer
10 views
SELECT inside trigger gives wrong result (it ignores the WHERE clause)
When I run this code inside an after UPDATE trigger:
SELECT * FROM `stock_totals` WHERE `product_id` = 2 AND `stock_place_id` = 3 INTO OUTFILE 'query3.csv'
FIELDS TERMINATED BY ','
...
0
votes
2answers
61 views
Slow Trigger performance when big batches
I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when ...
-2
votes
1answer
48 views
MySQL trigger calling stored procedure always get null value for out parameter of stored procedure [closed]
My stored procedure OUT parameter, always return a null value.
Here is sample Table, Trigger and Procedure code.
Table: test
Columns:
id - Int
status - enum(‘pass’, ‘fail’)
status - enum(‘pass’, ‘...
2
votes
1answer
69 views
SQL Server-Table has many AFTER INSERT triggers that insert into the table itself
I purposely created this example to represent my problem.
I created table like this:
CREATE TABLE a
(
id INT
)
I then created 2 AFTER TRIGGER like this:
CREATE TRIGGER insert_a
ON a AFTER INSERT
...
2
votes
1answer
36 views
ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER
I give an example to show my problem. I create a table as the following:
CREATE TABLE a
(
id INT
)
I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"
CREATE TRIGGER [dbo]...
1
vote
0answers
23 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 ...
0
votes
1answer
37 views
How to deny viewing definition of server level trigger?
I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc.
I've tried 2 options:
DENY VIEW ANY DEFINITION ...
0
votes
0answers
15 views
expected output not comes for the following trigger [duplicate]
USE `test`;
DELIMITER $$
DROP TRIGGER IF EXISTS test.new_table_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`new_table_AFTER_INSERT` AFTER
INSERT ON `new_table` ...
0
votes
1answer
23 views
Is it correct way having status or Summary columns in parent table or should choose another precedure?
My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below:
Jobs (JobId, JobType, CurrentStage, AssignedTo)
JobActions (JobActionId, JobId, ...
0
votes
3answers
29 views
before update trigger on sql server
I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and ...
0
votes
1answer
15 views
MySQL trigger: if record exists just update
There is a MySQL database with the following table reviews(reviewer, product, score, title, comment, creation_date, last_modified).
It is needed a trigger before insertion to check if the user has ...
0
votes
1answer
26 views
Help with making a trigger Mysql
I have the following two tables:
TABLE 1:
+-----------------------------+--------------------------------------+------+-----+---------+-------+
| Field | Type ...
0
votes
1answer
16 views
Are triggers and their results visible in same order as original operations?
I've created simple trigger
CREATE TABLE data (
id int,
v1 text,
v2 text
);
CREATE TABLE events (
id serial primary key,
v1 text
);
CREATE OR REPLACE FUNCTION emit_event_for_v1()...
0
votes
0answers
16 views
PostgreSQL trigger for user access on creation of new schema or table
I'm looking to create a trigger which will grant group access to both a schema or a table when created within a database.
Admittedly I might have this wrong and it may be done through a different ...
0
votes
1answer
40 views
Why some triggers run exactly at the same time?
I wrote a simple audit with triggers. when a trigger called, the data inside the trigger (inserted and deleted rows) are sent through broker to a procedure to write them in the Master Audit Table. ...
0
votes
2answers
37 views
Trouble when accessing old after deleting a row in postgrsql
When I delete an athlete from my database I want to take the sum he has raised and divide the sum between all the other athletes in my database.
When I run my code "addBalance" becomes Null and ...
0
votes
2answers
65 views
update the timestamp on column when data is filled in another
I am new to Postgresql and SQL in general. I have a table that looks like this
id (primary key)|temperature|temperature_timestamp|humidity|humidity_timestamp|...|parameter|parameter_timestamp
I ...
0
votes
0answers
24 views
Postgres Notify via trigger function that SELECTs the result of window function using new row?
I have a query that runs a select that operates on a range of rows to find and return the max, min, etc., e.g.:
SELECT
time,
device_id,
MAX(high) OVER(rows between 4 preceding and ...
0
votes
0answers
42 views
Trigger SQL with Consecutive number
This is instruction: Create a SQL trigger to generate the NumeroNomina with the format : IdEstado - NumeroConsecutivo (consecutive number).
Until now I have a trigger which retrieved the IdEstado and ...
2
votes
0answers
110 views
Why is an Oracle Package Variable Intermittently Incorrect after Multiple Updates
I am supporting an application that runs on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.
We have a table that stores hourly data that uses triggers call a package to ...
0
votes
1answer
33 views
Syntax for correctly capturing current user in a trigger
So this worked as a test:
CREATE TRIGGER `Metrosky_spotters_2020_au`
BEFORE UPDATE
ON `Metrosky_spotters_2020`
FOR EACH ROW
SET NEW.trnmswid = 5000;
Now I'm getting syntax error substituting "...
1
vote
1answer
32 views
AFTER UPDATE trigger that modifies the table on which it is defined
On MySQL 8.0 Trigger created:
DROP TRIGGER `Metrosky_spotters_2020_au`;
DELIMITER ;;
CREATE TRIGGER `Metrosky_spotters_2020_au` AFTER UPDATE ON `Metrosky_spotters_2020` FOR EACH ROW
UPDATE ...
0
votes
1answer
52 views
Trigger on indexed view
I created an indexed view with a INSTEAD OF trigger on it, but it's not firing when inserts happen at the base table level.
This is the plan that a normal insert generates (note the clustered index ...
