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 ...
