Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
2 votes
1 answer
23 views

"column new.total does not exist" error while creating a trigger

I have spent a lot of time investigating this error and couldn't find a solution. I am trying to create a trigger function and a trigger and then call the procedure when the when () condition in the ...
user avatar
  • 21
1 vote
1 answer
57 views

Use variable as column name

I'm trying to create trigger in PostgreSQL 14: create or replace function add_stats_to_player() returns trigger as $add_stats_to_player$ declare equipment_id int := new....
user avatar
  • 11
2 votes
1 answer
48 views

Understanding `WHERE x = NEW.x` Syntax

I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application. ...
user avatar
2 votes
1 answer
44 views

Trigger works for UPDATE, but for INSERT

In my database schema an organization can have multiple addresses but only one default address. I'm trying to create a trigger where if the is_default column is set to true on an insert or update, it ...
user avatar
0 votes
0 answers
35 views

During the trigger execution getting error : ORA-00604: error occurred at recursive SQL level 1 ORA-00900: invalid SQL statement

When I am trying to execute this trigger getting following error: CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE lv_sql VARCHAR2(5000); obj_name VARCHAR2(...
user avatar
1 vote
0 answers
22 views

Prevent Dacpac dropping columns via ALTER_TABLE trigger in TSQL

We're using SSDT to deploy database updates to clients, but sadly they will soon have the ability to add their own columns to tables in their databases. Obviously these won't be included in our model, ...
user avatar
  • 11
2 votes
2 answers
156 views

how to update one column with trigger mysql

i have 2 tables the first one is Table KEYS game keys WOW AAAAAAA-AAAAAAA-AAAAA WOW BBBBBBB-BBBBBBB-BBBBB L4D2 AAAAAAA-AAAAAAA-AAAAA TABLE inventory game country language pieces WOW USA EN 0 ...
user avatar
  • 31
2 votes
1 answer
53 views

Multiple Computed, Dependent Columns on INSERT in SQL Server?

I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/...
user avatar
2 votes
1 answer
53 views

SQLite: Can you use a DELETE trigger to delete other records in the same table?

In this specific case I'm using a SQLite database, but I believe this to be more of a conceptual question with practical implications. I have a nested-set model in my database and I would like to ...
user avatar
0 votes
0 answers
18 views

most efficient way to synchronize tow databases with minimum resources

i have two databases , one of them is in HFSQL server i use it as primary database. and the second is hosted in MySQL server . how can i synchronize the second(MYSQL) to follow my primary(HFSQL) ...
user avatar
1 vote
2 answers
217 views

Use default value instead of inserted null

I have a table definition in Postgres that use now() for timestamp and current_user() for auditing. date_created date NULL DEFAULT now(), edited_by varchar NULL DEFAULT "current_user"(), ...
user avatar
  • 263
0 votes
1 answer
143 views

Postgresql error syntax error at or near "END;"

Can someone explain why it throws error ERROR: syntax error at or near "END;" at this block of code: CREATE OR REPLACE FUNCTION find_equal(object objects []) RETURNS text [] AS $$ DECLARE ...
user avatar
0 votes
1 answer
106 views

How to handle possible NULL in a Trigger Updating a table without a Primary Key

I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
user avatar
1 vote
1 answer
54 views

Trigger uses a procedure or a function?

PostgreSQL version 10 and before use the term "Trigger Procedure" (https://www.postgresql.org/docs/10/plpgsql-trigger.html). But later versions use the term "Trigger Function". ...
user avatar
1 vote
0 answers
78 views

Postgresql trigger insert on another table after update and update other tables

I have a post table and when I soft delete a post I want to also soft delete rows on the comment and post_image tables using a trigger. I have an intermediate table deleted_post where I insert the ...
user avatar
0 votes
1 answer
32 views

Triggers: counting rows in a bridge table after INSERT

I'm using a trigger in order to make a Statistic Table. I have this many-to-many relationship where I have an Order that can have multiple Product's and viceversa. So after an INSERT on Order, using ...
user avatar
1 vote
1 answer
143 views

Postgres' conditional trigger: performance consideration

In my DB i need to make conditional trigger, and i stumbled upon this article. Discussed in the article, there are 2 options to implement the conditions: Option A: condition resides in the trigger ...
user avatar
  • 157
1 vote
1 answer
67 views

Deadlock reading table inside an update trigger

I am using Microsoft SQL Server 2019 with an UPDATE trigger that reads the table that is being updated. If the trigger is called simultaneously, there will be a deadlock situation. The trigger roughly ...
user avatar
0 votes
0 answers
18 views

phpmyadmin Triggers

welcome I have three tables in a database and I want to link them videos channels channels2 i have code SET @channel=(Select channelnumber from channels where flag='0' and channelnumber not in (...
user avatar
5 votes
1 answer
150 views

Why does UPDATE FROM fail and MERGE works on a View with an INSTEAD OF Trigger?

Sometimes for refactoring, I use a View to abstract the changes and use INSTEAD OF Triggers to mimic the previous functionality. I've run into error 414 (or 415) in the past UPDATE is not allowed ...
user avatar
  • 1,060
0 votes
0 answers
47 views

Is it possible to run a query for EVERY DDL change that occurs in a server?

I want to run a query on each and every database that is change in whatsoever DDL change possible. For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer ...
user avatar
  • 31
0 votes
2 answers
40 views

Oracle 12cR2 - After Logon Exception

Logon trigger as below: create or replace TRIGGER USER3.MY_TRIGGER AFTER LOGON ON DATABASE BEGIN IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER30') THEN IF SYS_CONTEXT ('USERENV', 'HOST') NOT IN ...
user avatar
0 votes
0 answers
18 views

What's the practical difference between these two triggers?

Say I have a table with a column to inform whether its row is a default of the application or not: CREATE TABLE IF NOT EXISTS "mytypes" ( "idx_type" INTEGER NOT NULL, &...
user avatar
11 votes
3 answers
515 views

MERGE into a view with INSTEAD OF triggers

I have a view with instead of triggers and I'm trying to use it with EF Core, which tries to batch inserts together in a form of merge statement. Here's my table and view: create table tbl (id ...
user avatar
  • 213
0 votes
1 answer
32 views

MySQL trigger communication between two tables

The problem is: I'm trying to find a value from one table where their vaccine choice is 'Janssen', I want the trigger to then copy their patient_id, the result of this will update the patient table ...
user avatar
1 vote
1 answer
148 views

MariaDB error with trigger and end statement

I am getting a MariaDB server version for the right syntax to use near 'END IF; END' I've tried to remove the semi colons sitting getting the errors, I thought I had done it in the correct syntax. Can ...
user avatar
1 vote
1 answer
79 views

Do existing connections immediately honor a change to the TRUSTWORTHY setting?

When you change the TRUSTWORTHY setting, will existing connections immediately honor this change? I had an experience that seems to suggest existing connections don't immediately honor the change but ...
user avatar
  • 439
1 vote
1 answer
46 views

Dynamic trigger function to delete oldest rows of a table

I am trying to generalize a trigger that deletes the oldest rows in a table as new rows are added. Since the number of rows to retain depends on the table, I am passing that number to the trigger ...
user avatar
0 votes
1 answer
48 views

How to create a trigger that generate a random specific output to insert into a table in MariaDB?

I have an account table which is laid out in 3NF: CREATE TABLE account ( account_id INT AUTO_INCREMENT, customer_id INT NOT NULL, account_type_id VARCHAR(15) NOT NULL, is_active ...
user avatar
  • 13
1 vote
0 answers
43 views

How to log changed table fields in SQLite?

I have a table that looks like this CREATE TABLE IF NOT EXISTS "ebooks" ( "id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, "author"...
user avatar
0 votes
2 answers
147 views

What is the difference between tsvector_update_trigger and ts_vector generated column?

Newbie to full text search, I have a feed_items table as follows feed_items (...title varchar, summary varchar, fullarticle varchar,...) I want to create a ts_vector column to search query from ...
user avatar
  • 246
0 votes
0 answers
16 views

Trigger implementation , did i do it properly?

FOLDER(CodFO,name,path,dim,ContainerFolder) FILE(CodFI,name,CodFO,path,dateC,dateE,dim) After a file is inserted into the FILE class , its path field gets updated after the instert in a way that it ...
user avatar
1 vote
1 answer
294 views

How to make mysql trigger to access another table's entry?

Say there is a DB with 3 tables Books table - contain book_id and its title Inventory - A list of book_ids in the inventory Logs - A list of log strings. Every time I add a new book_id to the ...
user avatar
  • 45
1 vote
1 answer
41 views

Postgres before insert trigger access field of a type

In the DB, I have a type and a table that one of it's rows is that type, such as this: CREATE TYPE state (last_active timestamp, ...) CREATE TABLE devices ( state state, ... ) For any interaction ...
user avatar
  • 113
1 vote
2 answers
88 views

What are the Cons of using triggers in place of count(*) for counting related entities?

What are the cons of using the below approach? What steps can be taken to prevent someone from incorrectly updating the count? Is the below approach an anti-pattern? If yes, what better approaches ...
user avatar
0 votes
0 answers
15 views

Actualizar en Trigger de delete en Mysql8

tengo una tabla en MySQL8 y una situación similar a esta: Tengo solucionado el trigger de insert, pero al querer hacer el trigger de delete (por ejemplo) me dice que no puedo modificar la tabla ya ...
user avatar
0 votes
1 answer
184 views

Trigger created with compilation errors

SQL> create table master(pno number(5) primary key,pname varchar2(20),price number(10,2),stock number(5)); Table created. SQL> create table trans(p_no number(5),no_pro_sold number(5)); Table ...
user avatar
0 votes
1 answer
39 views

Its possible create a trigger after every transact in all DB? (SQL SERVER)

I have 100+ databases in my instace of SQL SERVER and mostly is a legacy DB but i dont have a control of what is new and what is old. My idea is create a Trigger in all server for every transact and ...
user avatar
0 votes
1 answer
40 views

insert and update triggers do not fire on replicated table

I have created a publication and a subscription on two servers with wal_level = logical in my postgresql.conf. Replication is functioning normally. My problem is to track changes in the replicated ...
user avatar
0 votes
1 answer
80 views

Oracle 12c - Logon Trigger According To Machine Information

I want to create a logon trigger. Trigger will do the following: It will block access to the database based on certain users and certain machine information. Then it will give an information message ...
user avatar
0 votes
1 answer
115 views

create a trigger to update status on parent table based on values of child table

Table order columns - id status 1 confirmed Table trays columns - id,orderid(fk),status 1 1 done 2 1 processing I have to update the status of ...
user avatar
1 vote
1 answer
141 views

Column name as an argument of the function called in the trigger (PostgreSQL)

I looked at all similar questions, but it seems I didn't have enough knowledge to apply them to my issue. Take a look at the following code. The bottom line is that I have a function that updates the ...
user avatar
0 votes
0 answers
67 views

Unable to get FileStream Length with After Insert trigger

I asked this question on StackOverlow, but it mostly received comments about the use of variables and no answer. I program mainly in VB.Net, so my T-SQL knowledge is limited. The reason I'm populating ...
user avatar
  • 1
0 votes
0 answers
24 views

How to run both "before" and "after" update in trigger

I need to calculate prior values in a table before the column values are updated. Basically, I have to calculate the difference between the before the update and after update values. If there is any ...
user avatar
0 votes
0 answers
15 views

After insert trigger is not working on Aurora Writer instance

I have an After insert trigger on aurora writer instance and it is not inserting any records into the audit log table after the record is inserted in the table. This is my AFTER INSERT trigger ...
user avatar
0 votes
1 answer
2k views

ERROR: syntax error at or near "END" [closed]

I am trying to play around with trigger and found some samples online which I pretty much copied before except changed table name and row counts. But somehow I kept on getting syntax error and ...
user avatar
  • 115
0 votes
1 answer
57 views

Use trigger on DELETE to retrieve original SQL command and user who did it

I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012. What I need is the username that executed it and the command executed, then I can insert them on a log ...
user avatar
  • 1,413
0 votes
1 answer
273 views

Postgresql - Is calling a stored procedure in a trigger possible?

I would like to call a stored procedure using a trigger in Postgresql. Is it possible? Here is an example: create table foo(n int primary key, n1 int); create or replace procedure ...
user avatar
  • 123
1 vote
1 answer
110 views

Is it possible an AFTER DELETE per-row trigger is not run?

I've created a function which inserts in an audit table some data about the row that just has been deleted, and created an AFTER DELETE per-row trigger on the table I want to audit. This is working ...
user avatar
  • 297
0 votes
1 answer
185 views

Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)

Oracle 18c: In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line: Select SDO_GEOMETRY line vertices as rows ...
user avatar
  • 1,373

1
2 3 4 5
28