Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
0
votes
0answers
5 views

PostgreSQL rule to delete record before insertion, if the record has changed

I'm not sure it's possible to do this with a rule, but what I want to do is, when you insert a record: If it doesn't exist, just insert it as usual. If it exists, but each field is equal to what you'...
0
votes
0answers
17 views

Trigger having issues on partioned table execution - Postgres - 11

Postgres - Trigger having issues on partitioned table execution I am using postgres 11 version I am trying to execute a trigger on partitioned table with customized function and trigger function, that ...
0
votes
2answers
30 views

Oracle 12c - Insufficient Privileges When Trying To Create A Trigger With SYS User

I want to create a trigger for CRE schema. I'm trying to create this trigger with SYS user but I have insufficient privileges error. My trigger: CREATE OR REPLACE TRIGGER CRE.TRIGGER_NAME AFTER LOGON ...
0
votes
0answers
23 views

Postgres, trigger function, is it bad for performance?

I have added the following trigger function to one of my tables in my Postgres database. CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); ...
0
votes
0answers
9 views

Trigger function accessing newly updated data from another table

I have two tables A and B. In one transaction I update both tables. In the trigger function after update I want to select data from both tables, but the result is the old data. The trigger is on table ...
0
votes
1answer
34 views

Is it possible to make a trigger that does not allow INSERT?

I'm currently learning how to implement sqlite commands through C code and I've been trying to figure out the best way to ensure that no data can be inserted to a table (which will only happen during ...
1
vote
1answer
21 views

How do you combine multiple update statements for the same row using MySQL trigger

Background: Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger. Code: Here's a simplified ...
1
vote
0answers
30 views

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback. I'd like to ...
-1
votes
1answer
40 views

How to update records of two tables through trigger?

I have two tables as employee and employee_backup whenever i update employee table the trigger must update employee_backup table also : this is the piece of code i was trying to achieve specified goal:...
-1
votes
0answers
6 views

Creating a record from one table with a trigger in MySQL using a table name base off the source record

Currently I have a system that writes a record to one master table 'data' and the same record is then also duplicated in a customer specific table 'data_customername'. I'd like to have the MySQL ...
0
votes
1answer
23 views

After Insert Trigger WIth MS SQL Server

I think I have all my bases covered but wanted to ask someone who knows better than I. This is my syntax. UPDATE SI SET autoInsert = 0 FROM inserted WHERE SI.autoInsert = SI.autoInsert OR SI....
-2
votes
0answers
14 views

Oracle Database Dev. asked me for huge RAM [duplicate]

I have no idea what is oracle database and how its work but I am system admin and the Development department asked me to create 2 Linux server with 32 CPU and 100 GB RAM for each server and make it ...
0
votes
0answers
29 views

SQL Trigger fails retrieve new record in second query

Any advice on this will be appreciated This script created a table with new records. My script is failing to assign the "Inserted" ID to the query but manually assigning a value works (a) ...
0
votes
1answer
29 views

Create ORACLE TRIGGER which (INSERT OR UPDATE) in TABLE1 whenever a new INSERT is done in TABLE2

i'm trying to create a TRIGGER for my Oracle database. I Have 2 tables in it(table1 , table2) Table 1 has 3 columns (id, name, value). id can be non unique here Table 2 has 2 columns ( id, concat) id ...
0
votes
1answer
38 views

“To many rows being processed at one. Reduce and try again.” error in SQL Server Management Studio

I get this error when trying to update some rows from SQL Server Management Studio v18.5: Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch Start Line 0] To many rows being processed ...
0
votes
0answers
21 views

DB design for single Dependent table column to multiple Primary table

I have a scenario where records in the dependant table(say History) are created by two entities, one User and other Application. For which my purposed design is User ----------------- UserId (PK) ...
0
votes
1answer
34 views

How to automatically increment several Integer columns based on alphabetically ordered text columns?

Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in ...
0
votes
0answers
13 views

Catch contraint error inside trigger on the table being inserted/updated

I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452) Finding an answer to that is not ...
0
votes
0answers
25 views

In ORACLE 11g , triggers, how i can get EXCEPTIONS, BEFORE they can be trigged

I had a package, very extensive who cannot be modified nor debugged. So i wrote an trigger then to get the table info before the process crash, BUT, in testing of the trigger as i insert in the table, ...
1
vote
0answers
28 views

InnoDB trigger deadlocks itself?

I have a MySQL 5.7.22 database using the InnoDB engine. There's a table respondents that has 6 associated triggers - three of them make CRUD operations on a completed_respondents table, and the other ...
0
votes
1answer
15 views

Update main and second table with a before update trigger for message logging

I currently have the following testing schema: CREATE TABLE userMessage ( message_id BIGSERIAL PRIMARY KEY, creator_id BIGINT NOT NULL REFERENCES "User" (id), created_at BIGINT ...
1
vote
0answers
13 views

is there any method should i use for trigger update?

I try to modify the syntax of SQL trigger to mysql trigger [check 32:01 for reference] ]1 someone suggested me to use this syntax in mysql, however I got error in mysql. DELIMITER // CREATE TRIGGER ...
2
votes
1answer
34 views

Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column ...
0
votes
1answer
25 views

Creating trigger from one database table to another database table postgresql

I have two database say db1 and db2. And two tables user_auth_table in db1 and user_table in db2. Now I want to trigger a function which sync up few values in both tables for each new row or on each ...
0
votes
1answer
23 views

Throw an error from the trigger?

When trigger returns NULL, row is not inserted, but result of the operation is still reported as successful (INSERT 0 0). And in my opinion this is not logical, as one would probably want to handle ...
0
votes
1answer
56 views

Limiting the occurence of a value in PostgreSQL

Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need ...
0
votes
1answer
17 views

Triggers or views on frequently updating table

We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
0
votes
0answers
48 views

Logon Trigger IP Address Issue

I have this logon trigger to restrict IP addresses. It is the same trigger I have seen everywhere. However, it is not working as expected, and I was hoping someone could give some direction. I am ...
0
votes
1answer
46 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
2answers
34 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
29 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
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
30 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
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
33 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
24 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
18 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
33 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
22 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
40 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
41 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
51 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
44 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
18 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
22 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
20 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
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
31 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 ...

1
2 3 4 5
26