Database Administrators

Questions tagged [trigger]

Ask Question

Procedural code automatically executed in response to a database event.

1,116 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
1answer
31 views

Create an incremental number with year and month without updating the entire table using a trigger

My situation and code I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number....
1
vote
1answer
29 views

Can a MySQL trigger include a where clause

I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match. I have a database that stores animal genealogy. Each row stores ...
0
votes
1answer
9 views

updating associated records' tsvectors when the primary record is updated

I have the following trigger function that updates the object's tsvector column when its details change: CREATE OR REPLACE FUNCTION customer_search_trigger() RETURNS trigger AS $$ BEGIN ...
0
votes
0answers
18 views

How Insert data to child table in different schema using trigger function and auto updates for these child tables

I am trying to insert data to the child tables from different schema i.e parent table and trigger function were in schema 1 and child tables were in schema 2. It throws me an error while I am doing ...
3
votes
1answer
62 views

Scan on trigger Seek on SSMS [on hold]

I'm having a performance issue with a trigger when insert/delete on a table, it scan an index associated to a view, but if I execute the update manually it generate a seek. I updated statistics for ...
1
vote
1answer
22 views

Does a MySQL dump that contains triggers double data on import?

I have a quick and simple question to clarify. I have medium sized database (400 Gigs) running in MariaDB 10.2 with lots of tables and lots of triggers as well. I am planning to take a mysqldump and ...
0
votes
3answers
25 views

How to prevent trigger from overwriting values that were explicitly set?

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?): create temp table ...
0
votes
1answer
15 views

How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else

I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far. CREATE ...
0
votes
0answers
21 views

trigger to update if not exists in table oracle pl/sql

I want to create a trigger PL/SQL for the table: ap_supplier_sites_all (oracle EBS R12), in this table i have below information: vendor_site_id (primary key), vendor_id, attribute4 (varchar250), my ...
2
votes
0answers
43 views

Catching QUOTED_IDENTIFIER Errors in Trigger without aborting transaction

Is there a way to catch a QUOTED_IDENTIFIER error without failing the transaction? I have assembled a minimal test case here. The trigger must have SET QUOTED_IDENTIFIER ON because it uses XML data ...
0
votes
3answers
93 views

Check if the value from one table exists in another tables in SQL Server

I have one table (tbl1) with column ID, the values can be duplicated. I have others tables (tbl2, tbl3...) with column ID , values are unique. I want to write a trigger on insert row in tbl1 and check ...
0
votes
0answers
31 views

How to recalculate and adjust the entire row of time in and time out in sql server?

In SN = 1, I have an original schedule of 09:15:59 with my first time in is 09:50:57 so that I am late about 34 mins. In SN=2, 13:27:05 is my time out. In SN=4, I time in again of 15:40:13 that gives ...
1
vote
1answer
64 views

What is the fastest way to (session) lock table?

I have some triggers to log changes on a table to Log table. On insert and delete I add row to the Log table and for update I add two rows. The log table contain identity column, and I want the 2 ...
9
votes
3answers
1k views

How is a T-SQL Trigger that only fires on real changes made?

I have a table trigger on UPDATE and INSERT that adds a row to another table. It only needs to add a row if one of four columns are changed. I tried using IF UPDATE(col) to test for changes but it ...
0
votes
1answer
29 views

Delete Trigger - Linking to a table with a PK made up of three columns

I'm currently writing a number of "AFTER INSERT, DELETE" triggers that will be used to sync data between two identical tables, in different instances. The issue that I'm having right now, is getting ...
0
votes
2answers
37 views

How to convert UPDATE operation into INSERT using triggers

Given I have a table User with column status (active/inactive) these users may be attached with some other entities, so I do not want them to get modified unless explicitly specified. and for ...
0
votes
0answers
12 views

Oracle Flashback with triggers

Has anyone added triggers to the table that was established for flashback? The thought here is to capture change data, then add additional data to the row for offloading to another service without ...
0
votes
1answer
19 views

mysql triggers to multiple table and insert new id to table

I have 4 table: table a (id (auto_increment), id_b , id_c, id_d(foreign_key from table b,c,d) , column) table b (id auto_increment, column) table c (id auto_increment, column) table d (id ...
0
votes
1answer
31 views

Syntax error in simple trigger

I have a SQLite table with these fields among others Fld_QuoteNumberRoot string Fld_Revision integer Fld_QuoteNumber string I need to fill ...
-1
votes
1answer
21 views

Triggers and Tables with SQL and NodeJs

Hi I 've a application runnning with nodejs and SQL server, What I need is that every time I Insert a record into the ChargesTable I need to check, how may charges does my user have made, and every 5 ...
4
votes
1answer
83 views

Complex Delete Cascade Including Self-Referencing Table

Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
0
votes
1answer
32 views

POSTGRESQL: Refresh the materialized view which effect when there are change based on columns

I have a database in postgresql. There are several materialized view (matview) in this database. In each table, i have a trigger and have a function for trigger to refresh the matview, and it works ...
5
votes
1answer
99 views

Grant dbcreator only for databases matching prefix

In Microsoft SQLServer 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix. Is it possible to do this at the ...
0
votes
1answer
13 views

argument 'and' incorrect, sum values of attributes

excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries?...
0
votes
1answer
40 views

PostgreSQL Trigger to keep track of table changes

I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach: CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS $BODY$ BEGIN IF TG_OP = '...
0
votes
0answers
33 views

how to insert an if check in an insert

I have an after insert at the statment level, which must insert the positive or negative result in an attribute based on the count of the values of other attributes. But control structures cannot be ...
0
votes
0answers
18 views

MySQL Replicated table: How can I prevent a trigger chain?

I've written a trigger to log updates on a replicated table. The trigger inserts into a separate table. We use MIXED replication which defaults to statement-based. The trigger spins around and ...
1
vote
0answers
33 views

Is it possible to setup a CREATE_LOGON trigger that treats AD Groups differently from users?

Just started in a DBA role with a new organization and they gave me a bit of a head-scratcher. We've got a trigger on CREATE_LOGON that DENYs VIEW ANY DEFINITION & VIEW ANY DATABASE. This was ...
1
vote
0answers
47 views

Start dbms_services on standby database

I'm developing a trigger which starts and stops services when the pdb is open. The point is that my trigger doesn't stop the service if I execute the ALTER PLUGGABLE DATABASE ALL SAVE STATE; with ...
0
votes
0answers
24 views

Oracle Sequence using trigger for partitioned groups of data [duplicate]

Create table T1 ( PK NUMBER ,Grouping Varchar2(50) ,SequenceID Number ); commit; SELECT * FROM T1; PK GROUPING SEQUENCEID ---- ---------- --------- 1 ABC 1 2 ...
1
vote
2answers
55 views

After Inserting , Update / Insert into another table in pl/sql trigger

I am working on PL/SQL trigger i have two table TAG Table SR.NO ID TAG BRAND_ID ------ ---- ----- ---------- 1 10 ABC 99 MY_TAGS Table SR_NO TAG COUNT ...
1
vote
0answers
25 views

SQL Server 2017 Merge Not Running Triggers

Everywhere I am reading, when a merge statement happens, it will fire the triggers (INSERT, UPDATE, DELETE) in no particular order. I have a basic merge: MERGE dbo.Users WITH(HOLDLOCK) as tgt USING #...
0
votes
2answers
57 views

Oracle Custom Sequence of a partitioned group (Insert trigger)

Please help, need an insert trigger to insert the group_ID (next sequential number) of a partitioned group. Much like a dense_rank of repeating group of names. When a new record is created, if the ...
0
votes
0answers
39 views

How to deal with refrencing a foreign key to two tables?

I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible. The issue I have is with the section shown above as it is a sub-...
0
votes
0answers
41 views

How can i write an update trigger between two tables

i have two tables one is called defaulters and users .defaulters has mypin and debt_amount and users has mypin and debtor .i want a trigger that update debtor in users table if mypin exist in users ...
0
votes
1answer
18 views

MYSQL - Error in After Insert trigger affects the actual insert on the trigger table

I have two tables call it - table_1 and table_2 I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3. When the insert on ...
0
votes
1answer
53 views

SQL-Server schema audit trigger - possible to get old view/sp/function definition?

I'm using the below database-level trigger for schema-change-auditing. It works fine, but if I do (drop view - create view), it only stores the sql that created the view, not the one that is dropped. ...
0
votes
1answer
25 views

PostgreSQL Trigger to override insert on table with inheritance

We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
1
vote
1answer
44 views

Recursion on deletion trigger that calls a function

I have the following schema: CREATE FUNCTION myfunc() Returns trigger as $myfunc$ DECLARE someVal integer; BEGIN SELECT Count(*) into someVal FROM MyTable ...
0
votes
1answer
34 views

Update query in AFTER INSERT is ignored

I have this triggers, that to put it simple do this things: Before Insert:creates new record with new id,name and puts value in start time cols. After insert: updates value in end time cols. create ...
0
votes
1answer
57 views

Trigger not setting DATETIME correctly

I have the following table: CREATE TABLE assignment ( id INT NOT NULL AUTO_INCREMENT, course_id INT NOT NULL, title VARCHAR(128), assigned DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, due ...
-2
votes
1answer
27 views

I need help making a trigger in mysql that prevents/alerts when having the same Car_Plate more than once in the table

I need help making a trigger in mysql that prevents/alerts when having the same Car_Plate more than once in the table
0
votes
1answer
25 views

How to write a trigger to add some information before update?

I want this trigger to insert some information a table named into orders_log (which has six columns orderNumber, orderdate, customerNumber, status (before update), status (after update), action = ‘...
0
votes
0answers
39 views

Slow insert (trigger/indexes) processing

Whenever an insert is done in a table these tasks are executed: One Trigger (see down). Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
0
votes
1answer
52 views

how can i write a trigger that compare between two tables in phpmyadmin mysql [closed]

I have two tables that has rows that have same values. I want to write a trigger that will check for mypin in debtors tables and if mypin exists in debtors it fills the debtor row in the users ...
0
votes
2answers
97 views

“Fire” a trigger to populate columns that are NOT NULL?

Working out of my SQL book at the moment. The question is, Create a trigger based upon the ENROLLMENT table. The trigger should “fire” before an INSERT statement. The trigger will populate all the ...
0
votes
0answers
32 views

Insert values into a table using a trigger with values from stored procedure

I'm a newbie here. So, please feel free to correct me if my code or thinking approach can be worked upon. I am working in MySQL Workbench and MySQL 8.0 Command Line Client. I have a database called ...
0
votes
0answers
173 views

How to use MySQL trigger to reject duplicate entries?

I want to learn how to use mySQL triggers. This is an example. my example table : exam_results STUDENT_ID (primary) EXAM_NAME RESULT_1 RESULT_2 OPTION_1 I used the following example in ASP.net code ...
3
votes
4answers
246 views

I don't know how to fix ORA-04091 mutating trigger error

I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table. The error is: error ORA-04091: table USUARIO.LINEAS is mutating, ...
-1
votes
2answers
33 views

Prevent 'Last' Trigger from firing unnecessarily

We have a legacy application running on SQL Server 2008 R2. On DML operations on some of these legacy tables, I need to write data to a set of tables in another database under the same instance. ...

1 2 3 4 5 … 23