Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,269
questions
0
votes
0answers
10 views
Updating multiple fields in a related row within a trigger [closed]
I have a search_results table that contains all of the fields a user could search for. It includes fields populated from multiple tables along with a computed search_document column for when the user ...
0
votes
0answers
23 views
Using a trigger to check if a row exists in mysql — why so slow?
I am using a trigger to check if a row exists. The code below works, but it is very very slow. I suspect that it has to do with the way I am handling NULLs.
Any suggestion to optimize it?
DELIMITER $$
...
-2
votes
2answers
17 views
new row inserted in master table it will trigger another table to insert a row with current date or max [closed]
I am trying to set up a trigger in SQL server that when a new row inserted in master table it will trigger another table to insert a row with current date or max...can somebody show me some example
-2
votes
0answers
52 views
SQL Trigger question (update and delete) [closed]
There are 3 tables, bookstore, book, and time log,
How would I create an update trigger to record/insert the price into the price_logs table before updating the book table? Also record the user who ...
0
votes
1answer
28 views
Use insert trigger to copy a modified NEW record to another table, with unknown columns
I use a Postgres extension (promscale) that dynamically creates tables with similar schemas: the tables all have a 'time' and a 'value' columns, then a number of labels columns.
Upon insert in any of ...
-2
votes
2answers
51 views
How to prevent database shrinking
We have a 3° party application, with an application user who needs to be db_owner on the database (not my choice) on SQL Server system. All stored procedures are encrypted in this database. At some ...
0
votes
2answers
54 views
How to delete records with FK constraint using Delete Trigger
I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my ...
0
votes
0answers
7 views
Before deleteion trigger for self joined table sub rows
I want to write trigger before deletion to delete sub-children from same mysql table.
My table is samp_c:
**ID** **Name** **key** **Sub-key**
1 sample1 ab 0
2 Sample2 bc ab
...
0
votes
1answer
38 views
How To Tell if Trigger is failing
I have a trigger, that I want to try to update a field based off the value of another table. I have manually verified that the data exists in the other table so I know the join will work. However, ...
0
votes
1answer
45 views
“SQL command not properly ended” with trigger
Problem
No matter what I do, I can not get my trigger to work. It seems to always complain about SQL command not properly ended, ignoring SQL statement and so on.
Research
I have spent hours looking ...
0
votes
2answers
43 views
Trigger: Only raise error if related record was NOT created between now and last Sunday
I have a trigger that prevents inserts into a DELETES table under certain conditions:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE
v_upd_row NUMBER;
...
2
votes
1answer
103 views
What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?
My company logs DDL on one database using a Database Trigger, looking for DDL including:
ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
When I disable or enable triggers using ALTER TABLE x ...
0
votes
0answers
25 views
Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts
I have a simple table with a geometry column like so:
CREATE TABLE geomtable
(
gid serial,
geom geometry,
trip_count integer
);
I want a unique constraint on the geometry column, such that ...
1
vote
2answers
39 views
Using NEW on tables with generated columns
I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially ...
0
votes
0answers
29 views
Best way to log all changes at column-level for every table in a database?
I have a database consisting of several tables and in a separate table, I would like to store every column within every table and the last time the data in the columns was modified (i.e. following an ...
1
vote
2answers
35 views
SQL Server cluster node fail over details
I would like to know the SQL Server cluster node fail over details immediately as soon as the passive node is active as I have some dependency on active node. For that, I see sys.dm_os_cluster_nodes ...
0
votes
0answers
19 views
Mysql falls every day
My database Mysql fallas every day:
Looking at the tail -100 /var/log/mysqld.log
Shows:
2020-08-16 20:56:03 7f787c7fb700 InnoDB: Error (Duplicate key) writing word node to FTS auxiliary index table.
...
1
vote
1answer
58 views
How do I refer to tables in a trigger? / OR / How do I look up FK values to be added on insert automatically?
My goal is to have Oracle fill in FK values that correspond to the right PK values of a parent table.
I have this trigger:
CREATE OR REPLACE TRIGGER connect_purified_enz_to_produced
BEFORE INSERT ...
0
votes
0answers
48 views
Making a trigger to update FK based on columns in another table and an external table
I'm inserting data into a database and I want the FKs to be populated automatically based on other information that links each FK to its corresponding PK.
Here's my plan:
Create an external table of ...
2
votes
2answers
67 views
SQL Server Unique Constraint on two columns with an exception
Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns. However, if DocumentID = 'NA', I would like ...
0
votes
0answers
14 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
21 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
48 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
32 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
10 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
36 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
35 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
35 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
41 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:...
0
votes
1answer
24 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....
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
51 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
23 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
40 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
30 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
61 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
58 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
24 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
58 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
49 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
48 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
35 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
39 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 ...
