Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

0
votes
0answers
16 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
11 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 ...
0
votes
1answer
16 views

PL/pgSQL: NEW.field = value; does not work in AFTER UPDATE OF trigger [on hold]

I have the following instruction in my code NEW.field1 = value_for_field1; The trigger, which is defined as following CREATE TRIGGER mytrigger AFTER UPDATE OF field1 ON table1 FOR EACH ROW EXECUTE ...
1
vote
1answer
16 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
16 views

why does MySQL Trigger that uses cursor only fetch one value [closed]

I have created a Trigger after INSERT on the table AcademicYearTermLevel which uses a cursor value fetched from another query. I want to use each cursor value in a while loop and insert rows into ...
0
votes
1answer
30 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
48 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
25 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
24 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
34 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
33 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
70 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
20 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
25 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
91 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
31 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
40 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
40 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
57 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
56 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
26 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
33 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
32 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
21 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
50 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, ...
2
votes
1answer
45 views

Logon Trigger Unexpected Result

I have only server logon trigger and it is being used to prevent logins coming from an application/server combination. It works appropriately for that application/server combination and gives the ...
0
votes
1answer
25 views

MySql - filter bad words in multiple tables using triggers

I have for example two tables blog_posts and image and each record in these tables have a text field, that may contain a bad word in it. so the idea is, I want to check these records and change these ...
1
vote
0answers
14 views

Only allowing selected values of different linked tables be selected. MySQL Trigger

I have 3 tables. I have an instructors table and facilities table. They are linked by facility.facility_id=instructors.facility_id (i.e. A facility can have many instructors but an instructor can ...
0
votes
0answers
16 views

one to many relation how to create a new row with mysql trigger

i'm really new to mysql triggers so...i have 3 tables: a,b and c. table a is related with table b with a one to many relaion and table c is the table that get the triggered infromation from the 2 ...
0
votes
0answers
40 views

mysql trigger before insert

How can i make this trigger update my table DATA if there is no row to update. Until i insert data in other table lets called ABC, this table uses a after insert trigger to insert information into ...
1
vote
2answers
85 views

Trigger to update only if value is in lookup table

I only want to update the comment if a description for the event is found in the TagDescLookup Table. If the Tag isn’t in the Lookup table then don’t change the existing Value for the comment column. ...
0
votes
0answers
46 views

Postgres TRIGGERS to create csv file after insert on Foreign Table

I have a foreign table called vw_asset_register with over 5000 rows in there which I'm calling from MSSQL database. SELECT * FROM vw_asset_register. I always use this to create CSV file into my ...
0
votes
0answers
43 views

init_connect not executing

From this question I was able to make my init_connect = "CALL mydb.Warnings()", but a user login in does not triggers the SP. the SP is like: CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`...
0
votes
1answer
37 views

Syntax Error on Create Trigger

CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction FOR EACH ROW BEGIN IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN INSERT ...
1
vote
1answer
54 views

MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin

I have the following two Triggers in my MySQL DB that fire BEFORE/UPDATE and AFTER/UPDATE on the house_report_details table. The first deletes all records in my scratch table tmp_house_rpt_details and ...
1
vote
2answers
60 views

If count >= row then insert row (substitute for variables and else/if)

I do 99% php/javascript work so getting my head around SQL is always a struggle, sorry if I seem dense. So I've got a table that looks something like this: post_id | meta_key | ...
0
votes
1answer
32 views

FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision

In the 'pubblicazione' table I want to insert some fields of the tuple contained in the 'legge_approvata' table, but to do this the promulgation date must be in the same month and year as the date of ...
1
vote
2answers
61 views

Can a trigger create a TABLE when inserting data?

I have a trigger that keeps track of history of table dev by inserting a row in table dev_hist: CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$ DECLARE action_name ...
0
votes
2answers
21 views

Sorting ranking by trigger

I create a game in which players will gain levels. The basic information is in the table "char" (nickname, lvl, exp, money ...). I want to make a ranking that will be updated on a regular basis and ...
0
votes
1answer
39 views

In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp?

I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field. My current solution is like this: CREATE OR REPLACE FUNCTION ...
0
votes
0answers
40 views

MYSQL trigger syntax for CONCAT

I am creating a trigger which should be really easy, but I cannot get past the set @code line? What am I doing wrong? There is an ID column in the wp_users table. CREATE TRIGGER affiliate_insert ...
0
votes
1answer
26 views

What is a practical use of FOR STATEMENT triggers?

I understand very well the idea behind triggers, how and when to use them. I also see the practical use of a FOR EACH ROW trigger. But I lack to find a concrete example of when I would use a FOR ...
4
votes
1answer
62 views

Can a non-owner of a table be allowed to disable the table's triggers?

I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
1
vote
1answer
18 views

Open encrypted trigger of table in database (MS SQL Server Management Studio)

Microsoft SQL Server Question for Trigger in database-tables. Is there a possibility to view Code behind a encrypted trigger of a database table? I only see a key inside the Icon and rightclick "...
0
votes
0answers
13 views

SQL Server update trigger populate a variable using a select clause on another table

Hope you guys can point me in the right direction here! Using SQL Server I need to run an update trigger on a column in a table which increments or decrements a counter value in another table. ...
-1
votes
1answer
22 views

mysql trigger not allowing to select from another table

I have the following snippet in a trigger on a table named bugs: SELECT uat_reopened_stats.release_name (uat_reopened_stats.rate_bugs + qa_reopened_stats.rate_bugs + ...
0
votes
1answer
101 views

Pass parameter to trigger dynamically

I have a table on a mysql 5.7 db, containing say athletes with their mean, max, avg times in a specific sport. I have another table that lists some calculated statistics based on those values. I ...
0
votes
0answers
32 views

SSMA Selecting Specific Trigger

We are beginning work on migrating an Oracle Database to SQL Server and I have installed the appropriate SQL Server Migration Assistant. While I am not planning to use this to build the actual ...