Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
-1
votes
0answers
15 views
0
votes
0answers
42 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 ...
0
votes
2answers
28 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 ...
0
votes
0answers
17 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, &...
11
votes
3answers
382 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 ...
0
votes
1answer
21 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 ...
2
votes
1answer
35 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 ...
1
vote
1answer
75 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 ...
1
vote
1answer
18 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 ...
0
votes
1answer
34 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 ...
1
vote
0answers
20 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"...
0
votes
2answers
30 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 ...
0
votes
0answers
15 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 ...
1
vote
1answer
35 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 ...
1
vote
1answer
26 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 ...
1
vote
2answers
84 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 ...
0
votes
0answers
13 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 ...
0
votes
1answer
86 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
27 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 ...
0
votes
1answer
54 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 ...
0
votes
1answer
35 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 ...
1
vote
1answer
52 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 ...
0
votes
0answers
62 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 ...
0
votes
0answers
21 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 ...
0
votes
0answers
12 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 ...
0
votes
1answer
623 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 ...
0
votes
1answer
47 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 ...
0
votes
1answer
52 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 ...
1
vote
1answer
35 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 ...
0
votes
1answer
22 views

Postgres preventing update on "events" table but insert "warning" event

I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database. My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
1
vote
1answer
24 views

Calling a boolean function for Trigger WHEN conditions

The following, CREATE TRIGGER _trigger_insert_form_metadata AFTER INSERT ON my_table FOR EACH ROW WHEN _conditions_form_metadata(NEW) EXECUTE PROCEDURE _trigger_insert_form_metadata_function(); It ...
1
vote
2answers
145 views

Error: Dynamic SQL is not allowed in stored function or trigger

Im trying to convert multivalued column in Table A into rows of Table B(The data type of the column is varchar). For ex: Table A id value ae [1,2,4,5] ac [1,6] Table B id value ae 1 ae 2 ae ...
0
votes
0answers
21 views

Postgres: Triggers Not Created for New Partitions

I have a table that is partitioned into a hierarchy of partitions as follows A -> A_x -> A_x_y -> A_x_y_z Where x is the project id, y is the instance id and z is the month. I've created an ...
1
vote
1answer
200 views

Column constraint based on values in another column

I have a PostgreSQL table phase_steps, with the following example rows: phase_step_id|step_type|step_status| -------------+---------+-----------+ 1| RESEARCH| | 2| ...
0
votes
0answers
60 views

Postgres: Event trigger for logging all dbs changes to one table

I am trying to create an generic event trigger, which I set up in every future databases, I create. This trigger should update the main db's table every time one of the other databases have add or ...
0
votes
1answer
35 views

Postgres per-statement update trigger relationship between old and new transition tables

Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs My use-case is that I have a "shadow" table ...
0
votes
1answer
118 views

Oracle: "table is mutating, trigger/function may not see it"

(Yes, this is one of those question again...) I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server. I have a table I1 (called t_link in my ...
-1
votes
3answers
80 views

after insert trigger not firing after inserting with bcp

content of csv file TEST_1|sl2sysxbar301.dv.local|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 5528, 'event_string': 'ENERGY_CTRL_RIGHT_PROX_DOWN', '...
0
votes
1answer
33 views

Trigger updatedAt=NOW() for hunded-thousand rows will not actual [duplicate]

I am using SQL Trigger to update updatedAt column if there is a new data inserted/updated in PG 9.6. Here is the function that will be called by trigger CREATE FUNCTION sp_updatedatstamp() RETURNS ...
3
votes
1answer
391 views

Disabling triggers makes DELETE fast, but the table has no triggers?

I am deleting 130k out of 750k rows from a PostgreSQL table. The first time, it took 8 hours to complete the delete query. Second time, I added an index to the table, and rebuilt that index. Now it ...
0
votes
0answers
121 views

Database users are lost after restore - how to fix

We had many facilities that operate in areas where there is poor/no internet connection; the facility staff sent us monthly a backup of the facility database for us to restore it on our data center. ...
2
votes
2answers
129 views

Enforce that every company has at least one CEO

I've a requirement to ensure that every company has at least one CEO. Example database model with requirements db<>fiddle. Basically, a CEO can't be removed from a company without naming another ...
5
votes
3answers
182 views

Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many

We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision ...
1
vote
0answers
18 views

Update a PostgreSQL row with last user and timestamp

I would like to track who is the last user who updated the rows of a PostgreSQL table (v9) and when. This webpage explains how to do it with a trigger regarding the timestamp (I did not try it yet), I ...
-1
votes
1answer
47 views

how to get the email address from inserted data as the recipient in sp_send_dbmail

I would like to use the email address that is inserted in a column as the @recipient in sp_send_dbmail. This code is a trigger that I've been trying to achieve this with and is for test purposes only: ...
1
vote
1answer
514 views

Create a Hangfire job via SQL stored procedure

I know how to create Hangfire jobs via .NET C# code, but there are a few occasions where I'd like for a database trigger to execute a Hangfire job. I searched the web but found nothing so I attempted ...
2
votes
1answer
165 views

SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)

I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a ...
0
votes
1answer
133 views

Stored procedure to change string to datetime in MySQL

This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my ...
-1
votes
1answer
30 views

Why PG 13 does not allow deferring statement trigger constraints?

I noticed in PG 13 docs that only row constraint triggers can be deferrable. Why not statement constraint triggers?

1
2 3 4 5
28