Questions tagged [database-trigger]

A database-trigger is procedural code written in the current database [tag:DML], that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

1
vote
1answer
41 views

SQL Trigger - Check into other Table, If exists then Update else Insert

MSSQL I have two tables. TableA and TableB TableA +----+----+-----+ | ID |Name|Marks| +----+----+-----+ | 1 |ABC |50 | +----+----+-----+ | 2 |BCD |80 | +----+----...
0
votes
1answer
32 views

I got 2 Errors , first there is a ignored plsql statement and then there is a identifier which need to be declared?

I wanted to convert my MySQL Trigger to an Oracle Trigger but there are 2 errors which does not make any sense for me. First there is a PLSQL Statement ignored and second my identifier needs to be ...
-1
votes
0answers
12 views

Sync table data as agregate from select via trigger

I'm struggling to figure out if this is possible via SQLite triggers. I have rooms, rooms belong to a group. Now, groups are be only calculated via distinct select from rooms SELECT DISTINCT ...
0
votes
0answers
22 views

Get column value dynamically from Inserted and Deleted table in trigger

Following is the requirement and approach/design taken Requirement: Need to insert into History table for an update on any column of a table, if and only if the value of the column has changed. Need ...
0
votes
1answer
20 views

MySQL Trigger Function (between 3 different tables)

I have 3 tables (written as important parts, not all data): tableDeviceStatus: device_id status --------- ------ device01 15 device02 20 tableDeviceTrigger: device_id ...
0
votes
2answers
36 views

Need help in converting Oracle trigger to PostgreSQL

I need help in converting the below query to postgres. Any help is greatly appreciated CREATE TRIGGER REQUESTOR_TRG BEFORE INSERT ON REQUESTOR FOR EACH ROW BEGIN <<...
0
votes
1answer
36 views

Problem create tigger for autoincrement on phpMyadmin

I would like to make a trigger after delete that take the value max ID + 1 for my autoincrement. Here is my current code with the error from MySQL: DELIMITER // CREATE TRIGGER form_id_autoincrement ...
0
votes
5answers
50 views

ORACLE11g autoincrement

Error code 4098, SQL state 42000: ORA-04098: trigger 'HR.TRGM_ID' is invalid and failed re-validation I'm getting this problem. What should I do? create table kids( id integer primary key, ...
1
vote
0answers
37 views

Postgres: Trigger on FOREIGN TABLE

I would like to use postgres_fdw and house a FOREIGN TABLE in my database. Is it possible to define a trigger on the local server for this FOREIGN TABLE that recognizes an INSERT event on the remote ...
0
votes
0answers
45 views

How to create an Oracle trigger that updates another table?

Imagine two tables: TableA with order headers and TableB with order items Uniqe link between the tables = ID I need (I think) two triggers: When a (date) field TableA.Q is updated, all (date) field(...
0
votes
1answer
33 views

Does trigger works with order?

I created trigger which working after every insert and for each row My question is in case I’m having like 1000 records which inserts together or working with websocket. Does the trigger is working ...
0
votes
1answer
43 views

Error from trigger function: ERROR: column does not exist

I created a trigger: CREATE OR REPLACE FUNCTION public.delete_user() RETURNS TRIGGER AS $func$ BEGIN EXECUTE format('UPDATE public.user SET %I = %L WHERE state = %L', "state", "active", "...
1
vote
1answer
17 views

Trigger function that Updates some columns after update of a specific column

I have the table Ticketsforsale: Ticketsforsale(ticket_id, starting_date, price) I need to create a trigger function which , in case of update on "starting_date" , will replace the old "...
0
votes
2answers
44 views

Statement level trigger to enforce a constraint

I am trying to implement a statement level trigger to enforce the following "An applicant cannot apply for more than two positions in one day". I am able to enforce it using a row level trigger (as ...
2
votes
2answers
37 views

Ora 06512/04088 triggers errors when INSERT INTO statement

I'm at work for a trigger which provide a "domain" for column Molteplicità in a table called Partecipa using a function. The trigger I've created is the following: CREATE OR REPLACE TRIGGER ...
-1
votes
1answer
34 views

MySQL Trigger update after an insert

What is the right syntax for an update after an insert on a sql trigger? I am trying: CREATE TRIGGER movimentacoes AFTER INSERT ON mov FOR EACH ROW BEGIN UPDATE cooperados ...
2
votes
2answers
29 views

Why am I getting “A sql/plsql compilation error occured.” error because of semicolons in trigger creation?

I am trying to create a trigger using 'Oracle SQL Developer' in 'Oracle 11G'. But every time a try it I get 'ORA-24344: success with compilation error' message and my trigger gets partially created ...
0
votes
1answer
16 views

Run migrations using db.create_all() [duplicate]

I needed to write my own Trigger to be able to update some search vectors in my Postgres database and alembic/ Sqlalchemy weren't able to autogenerate these. For that I added my custom SQL into a ...
0
votes
1answer
16 views

unknow system variable: “new.id” is not recognized

Let's say 6,7,8 are all taken, and l tried to insert data with id = 6. The purpose of the trigger is to find the next available number. However, MySQL does not recognize new.id Or, can l alter ...
0
votes
0answers
20 views

Postgresql update counts after insert ,update ,delete for join tables

I want to create a trigger function to update books_count of the campaign if I insert/update/delete a row of the books at the books_category table. For example, my table categories are : ...
0
votes
0answers
31 views

SQL Server: conditional trigger on imported Line

I am trying to built a trigger on the [Catalog] table in the database [ReportServer]. The idea is: when a a report is published, I would like to test if it is built with a VIP data source, and if ...
-2
votes
1answer
60 views

Creating a trigger from SQL view

I have two tables that are updated by one function (GLPOST and GLPOSTO). I have created a view (GLREP) based on these two tables that transposes the rows of GLPOSTO into columns as this is how I want ...
1
vote
2answers
67 views

How to write triggers to enforce business rules?

I want to create triggers for practicing PL/SQL and I sorta got stuck with these two ones, which I'm sure they're simple, but I can't get a hold of this code. The first trigger forbids an employee to ...
0
votes
0answers
41 views

Getting Mutating error while running trigger

I am getting table is mutating error when I try to update the table update flight set captainStaffID='S0009' where flightid='000000043'; Here is the trigger: create or replace trigger trigger1 ...
1
vote
2answers
73 views

IF(:old.actDepartDateTime IS NULL) THEN

backs up the flight ID, ticket number and seat number for any deleted ticket if the ticket corresponds to a flight that has not yet departed. CREATE SEQUENCE generateKey START WITH 100 ...
0
votes
0answers
27 views

How to create a audit table for a given table to log all the changes to the table

How to create a audit table for a given table to log all the changes to the table For example, there is a table called person with 3 columns (id,name,age). I have to create a new table with name ...
1
vote
1answer
25 views

How to disable an existing Trigger in SAP HANA Database?

I want to disable an existing trigger in SAP HANA database. Trigger name is UPDATE_SERIAL_CHANGELOG I tried executing below script however I'm getting error:- ALTER TRIGGER "CM_DB"."TRIGGER ...
0
votes
0answers
25 views

NEW.x value on after update trigger if value didn't change

So i have a mysql trigger on AFTER UPDATE UPDATE `Pipas` SET `bolStatus`= (SELECT IF(NEW.progreso = NEW.campos_totales, '1', '0')) WHERE Pipas.pipa_id = NEW.pipa_id My question here is, if i make ...
0
votes
0answers
31 views

oracle failed triggers validation

Imagine there are 2 triggers like create or replace TRIGGER A AFTER CREATE ON SCHEMA and create or replace TRIGGER B AFTER CREATE ON SCHEMA (note on schema). Both are invalid (fail to execute) ...
4
votes
1answer
42 views

Handle NULL values ​in trigger of PostgreSQL views using DEFAULT?

I wanted to have an explanation on triggers of Postgres views. To make clear what I want to ask, I'll give you a very simplified example of my case. In this example we have two tables (table_a, ...
0
votes
0answers
46 views

DB Trigger for Sending an email not working

I have a custom database sql Trigger which inserts a new row if the previous condition has been satisfied. The problem is, the confirmed_Erp flag on the database does not get actioned if I include ...
0
votes
1answer
25 views

How I make a trigger for add in a field of a table in MYSQL

I want to make a trigger on MYSQL that increases the value in the field of a table DROP DATABASE IF EXISTS Lollipop; CREATE DATABASE Lollipop; USE Lollipop; /* Sucursales */ DROP TABLE IF EXISTS ...
0
votes
1answer
18 views

How to create trigger that gets value form table and update different table

DELIMITER // CREATE TRIGGER after_game_status AFTER UPDATE ON games FOR EACH ROW BEGIN DECLARE player1,player2, score1, score2 INT DEFAULT NULL; IF ( NEW.status = 'finished') THEN ...
0
votes
0answers
21 views

Is there a way of side-stepping error #1442 in an INSERT trigger?

Problem: When a row is INSERTed, I must use a trigger to (conditionally) insert another row in the same table. I must put "NEW.rowID" in a different field of the "another" row (rowID being the primary ...
0
votes
2answers
41 views

How to set a trigger to insert in another table from another schema?

How to set a trigger to insert in another table from another schema ? Brief explanation Considering schema "S" and schemas "1" to "n". Consider that Schema "S" has table Users and table Company. ...
-1
votes
1answer
25 views

Why are trigger pseudorecords placeholders for bind variables in trigger body but not WHEN?

The documentation states that: In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable. Reference the field of a ...
0
votes
0answers
21 views

How do I know when bind variables are required? [duplicate]

I have a simple trigger that uses the following code: CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF mgr_start_date ON department FOR EACH ROW WHEN (new.mgr_start_date < old.mgr_start_date) ...
1
vote
1answer
39 views

ORA-00942 error when running code to create trigger that inserts rows from data dictionary views

I am building a table that takes in table/view name, created date and the name of the creator as soon as a table is built. I have written a trigger to do the same which uses the insert statement and ...
0
votes
1answer
23 views

check if fields of a sqlserver table are empty/full in a computed field

i need to add a computed field in a sqlserver table that shows me if the other fields of the table are empty or full (all, some of them, none) the field should contain: 0=all the fields are empty(...
0
votes
0answers
31 views

Count is not working in trigger using condition

Hi could you please suggest I am trying to check if count =2 When Condition meet (DA.matr_ID = '478') or (DA.matr_ID = '40') then insert into table, but in any condition like (DA.matr_ID = '479') ...
0
votes
1answer
28 views

table is mutating, trigger/function may not see it; needs after/row level query

How to rewrite a trigger to avoid the error "BB_BASKET is mutating, trigger/function may not see it" Homework instructions: Create a trigger named BB_SALESUM_TRG that updates the BB_SALES_SUM ...
0
votes
1answer
37 views

Having issue with a trigger that fires after a row in a table is updated/inserted/deleted

I have a small system that I've had to create for an assignment. It's meant to make viewing relevant data in the database easier for "management". I have three tables: CLIENT just contains a ...
0
votes
2answers
39 views

creating a trigger that updates a table when a column in a different table is updated

For some reason I'm having a hard time fully understanding triggers. For my homework assignment I need to create a table that holds product id, total sales, and total quantity sold for each product (...
-1
votes
1answer
35 views

create a trigger to update values

I have these two tables Project (projID,TotArticles) Article (prodID,ArticleID) How can I create a trigger to update by 1 the total amount of article every time someone published an article on it? ...
0
votes
0answers
18 views

How to run dbms_application_info.set_client_info on every transaction in Spring

I'm developing a Java web application which uses the Spring framework and JPA to store data in an Oracle database. The application has an audit log, which is updated by triggers, which read the ...
0
votes
2answers
36 views

SQL: Write a trigger that stops a row from being inserted if Person already owns a Store

I have the following table called Manager: Store | Person ABC Ms. Elara XYZ Mr. Saros DEF Ms. Orion I am learning about triggers in PostgreSQL and I am trying to write a ...
-1
votes
1answer
67 views

How do I correctly compare values across tables with triggers? (PL-SQL)

I am currently not able to get a trigger working in Oracle SQL. I am trying to compare values across tables. More specifically, I want to see if a date is bigger / later than another date, depending ...
0
votes
0answers
15 views

Trigger After update showing this(ERROR 1093: 1093: You can't specify target table 'uim' for update in FROM clause)

When I try to update the values in tables and after an update, trigger get fire automatically I'm not able to update this as I'm getting error ERROR 1093: 1093: You can't specify target table 'uim' ...
1
vote
2answers
51 views

Postgresql ignoring 'when' condition on trigger

A trigger seems to be ignoring the 'when condition' in my definition but I'm unsure why. I'm running the following: create trigger trigger_update_candidate_location after update on candidates for ...
0
votes
0answers
15 views

firebase APN push notification TypeError: The header content contains invalid characters

I want to send a push notification from a firebase trigger to an apple iPhone using typescript. https://firebase.google.com/docs/cloud-messaging/http-server-ref https://firebase.google.com/docs/...