Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,104
questions
1
vote
1answer
61 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 ...
3
votes
2answers
71 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
9 views
Db triggers action [closed]
I am trying to make a simple inventory system with automatic reordering. In the inventories db I have a list of items in the inventory and I have a critical value entry for each item, a value that ...
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
34 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
8 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
28 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
20 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
73 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
91 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
35 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
31 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
15 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
32 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
43 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
49 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
17 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
53 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
37 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
17 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
49 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
20 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
37 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
52 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
37 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
48 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
81 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
30 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
87 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
183 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
vote
1answer
51 views
executing a stored procedure on a monthly schedule
I'm looking to create an invoicing procedure that executes automatically once a month. The procedure name is INVOICE_PROC.
I have tried to use a trigger to execute it but I can't get it to work. ...
1
vote
1answer
44 views
how to prevent UPDATE of existing rows/record but allow INSERT using PostgreSQL trigger?
SOLVED
Edit: Trigger function and trigger are working fine with no errors, problem was with my database itself, dropped the database, created new, it's working now.
How do I prevent UPDATE on any ...
0
votes
2answers
67 views
Triggers vs. Jobs in SQL Server
I’m designing a database with a table that automatically gets loaded with a new record every 15 seconds (each record will have a 'timestamp' field and around 50 ‘float’ fields). Each record in this ...
0
votes
1answer
109 views
Trigger for multiplying two columns from different tables with a functions in mysql
I have been endelessly trying to get a trigger to work, with many different variations and all ending in errors.
I have finally just decided to ask for help, since im still a beginner at this.
So ...
0
votes
0answers
39 views
Oracle trigger starting services in standby
I'm using Oracle 18.3 with pluggable database and I have an architecture like this:
host 10.0.0.1 has two instances: 1 PRIMARY and 1 STANDBY SYNC
host 10.0.0.2 has one instance: 1 STANDBY ASYNC
I ...
-1
votes
1answer
22 views
Lay out two triggers in different tables
So I have two BEFORE INSERT triggers. One is:
DELIMITER //
CREATE TRIGGER Carperperson /* This Trigger Blocks a customer from renting two cars on the same name twice on one day. */
BEFORE INSERT ON ...
1
vote
1answer
52 views
Mimicking cascading foreign key referencing system table
I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables.
On INSERT it ...
0
votes
1answer
35 views
Making a field greater than the limit unique
Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome.
I am currently designing a set of database tables for a project I am working on....
0
votes
1answer
47 views
getting the error message from another trigger in sql server
i have a table 'Event' and it contains a trigger trEvent which updates table 'Position', and table 'Position' has a trigger trPosition which validates the Position records.
whenever trEvent is fired ...
0
votes
0answers
25 views
Is it possible to execute a postgresql function when any view is refreshed?
I want to alert my application that are utilizing views when the view is refreshed. Is this possible using PostgreSQL built-in functionality? If not are there any other approaches to tackle this?
0
votes
1answer
92 views
Overhead of triggers and foreign key constraints?
I have a sqlite database which is accessed by two programs. There are a number of invariants/constraints which should always hold. In addition to the usual foreign key constrains between tables, ...
