Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

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

1
2 3 4 5
26