Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

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

1
2 3 4 5
25