Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
0
votes
1answer
8 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 ...
1
vote
1answer
519 views

Error in creating trigger

I have 4 tables: event: event_id(p.k) | uid | circle_id(f.k) activity: uid | performed_activity_id(f.k->event_id) | activity_type_id follow: follower_id | circle_id(f.k) notification: sender_id ...
0
votes
0answers
17 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 ...
0
votes
1answer
540 views

Postgresql: Insert trigger function from select query, plus static values pulled from the new.<record>

We're having an issue writing a trigger function that involves 3 tables: CustomerProfile, DropdownStats and OrderDetails. A simplified schema of each is as follows... CustomerProfile table...this ...
0
votes
1answer
566 views

mysql trigger to trigger only if record doesn't exist

The following trigger : DELIMITER // CREATE TRIGGER auo_mtltp AFTER UPDATE ON master_table_linfomasltp2016 FOR EACH ROW BEGIN IF (NEW.IMHQ_solicitado = 'SI') THEN ...
5
votes
1answer
4k views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS $...
9
votes
1answer
24k views

Can mysqldump dump triggers and procedures?

Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db? Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it....
3
votes
1answer
56 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
0answers
69 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 ...
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 ...
2
votes
1answer
167 views

Setting up a cascading trigger on a parent table

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards: Table "postcards" Column | ...
13
votes
3answers
10k views

Is it possible to get execution call stack in a trigger?

I have 10 stored procedures and each of them does INSERTs into one tableX. Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ? ...
1
vote
3answers
5k views

Adding Row Version to SQL Server table

I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
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 ...
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
1answer
178 views

Get the deleted value using while looping through the columns

I wrote a trigger to do auditing. To capture all the changes, I loop through the list of columns. The problem I'm having (see code below), when I try to set the OldValue using the @fieldname, I get ...
1
vote
2answers
92 views

Is there a way to reference a column dynamically within a trigger?

I asked a similar question a few months back and I'm improving upon my solution! I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of ...
2
votes
0answers
42 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 ...
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 ...
2
votes
2answers
176 views

Deadlocks With Trigger Based Replication

A table ORIGINAL exists with the following structure: ID VARCHAR Length 10 (key) VALUE VARCHAR Length 10 (non-key) A table REPLICATION exists with the following structure: ID ...
0
votes
1answer
82 views

insert multi on a table handled by trigger

I have this table CREATE TABLE `users` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(20) DEFAULT NULL, `email` varchar(255) NOT NULL, `md5_email` char(32) NOT NULL, `entered` ...
0
votes
1answer
109 views

Oracle SQL Complications With Multiple Triggers

Hey all so I am creating a DB that is used going to be used for managing utilities of a system. My DB has 3 tables: Users, Utilites, and Files (leaving Files table out because question shouldn't ...
0
votes
2answers
257 views

How to create a trigger in oracle to prevent overlapping date ranges

I'm learning Oracle and I had a problem. Chat table: CREATE TABLE training ( id_training NUMBER, id_user NUMBER, start_training DATE, end_training DATE ); I want to ...
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
3answers
143 views

Trigger INSERTING into a row

I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one. CREATE TABLE ...
1
vote
1answer
9k views

Before and After update trigger on same table

I have a table with 4 columns qty, qtydiff, price and value. I want a after update trigger that fires when qty row values change (more than one rows). The Before trigger should read and store the ...
0
votes
1answer
95 views

ADD and DROP triggers frequently on a database

I have a table from which I am deleting the data. Whenever a row is deleted, the row matching the data from other tables must also be deleted. So I am planning to achieve the same using triggers. The ...
1
vote
2answers
172 views

Trigger - If time between?

I wish to use a mysql trigger that will update a column called peak to 0 or 1 based on if the CURTIME() is between 08:01 & 11:59. Can anyone help with this trigger?
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 ...
0
votes
1answer
1k views

MySQL : How to know if Trigger was fired

Someone can help me please , I want to log error about triggers in MySQL 5.5 I'll like to know if it's possible to know if a trigger was'nt fired ?
0
votes
3answers
157 views

Move new data records automatically to another database in oracle xe

What's the best way to move all the data from a table on my local oracle database to an identical table on another pc? I will have to transfer the data to the second database as soon as it's written ...
1
vote
1answer
135 views

Dynamically records not getting to cursor in trigger

I am using cursor in trigger in MySQL. I want to get records of recently added id. I am using this: CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_property_ratings` AFTER INSERT ON `node` FOR ...
0
votes
2answers
157 views

How to disable triggers from nesting/recursing when accessing the same table

So I have this data +----+----------+-------+---------------+----------------+------------+--------+ | id | date | accid | accid_currbal | Action | Desc | Amount | | 1 | 05/06/17 |...
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
2answers
101 views

Elements inside a container: How to perform atomic operations on them?

In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes. Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null). Each element ...
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
0answers
11 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
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 ...
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 ...
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
153 views

Insert Timestamp on value change from other table

I have an EvidenceTimeStamp table that is triggered with data when the case table has data inserted into it. EvidenceTimeStamp: CaseID EvidenceNum EvidenceType Evidence_Initiated_Timestamp ...
-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 ...
1
vote
1answer
117 views

Mysql auto insert record in primary table if not exist

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - ...
0
votes
1answer
123 views

Create trigger that Find, Parse and Insert to another table After inserting on table

I want to create a trigger that will insert to another table after inserting on my table. Scenario: I will insert on my inbox table. Then, on my inbox table there is a field named Msg. Next, I want ...
1
vote
2answers
54 views

Automating Redshift data dump to a PostgreSQL DB every hour

GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I ...
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
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 ...
1
vote
1answer
131 views

Trigger in MySQL upon changing stock status

This are one of the requirement that have to be done for my assignment and I am having a trouble on how or where do i start. my lecturer told us smth having a trigger and he is reluctant to guide us ...