Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
0
votes
1answer
293 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 ...
0
votes
1answer
97 views

Get previous field's value in MySQL trigger

I am using a MySQL database and I have this kind of table. IDRefHandler 1 x    299       2 x    300   &...
1
vote
1answer
141 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) - ...
-2
votes
0answers
13 views

Oracle Database Dev. asked me for huge RAM [duplicate]

I have no idea what is oracle database and how its work but I am system admin and the Development department asked me to create 2 Linux server with 32 CPU and 100 GB RAM for each server and make it ...
-2
votes
0answers
29 views

Oracle Database Dev. asked me for huge RAM. Please help [closed]

I am system admin and the Development department asked me to create 2 Linux server with 32 CPU and 100 GB RAM for each server. they already have too many server with hardware like that. I feel they ...
0
votes
0answers
28 views

SQL Trigger fails retrieve new record in second query

Any advice on this will be appreciated This script created a table with new records. My script is failing to assign the "Inserted" ID to the query but manually assigning a value works (a) ...
2
votes
1answer
142 views

Notification of row entries

I have SQL Server 2012 machine housing a database running an off the shelf application with a users table. This users table contains PII enterable fields. Per requirement none of our databases can ...
0
votes
1answer
34 views

“To many rows being processed at one. Reduce and try again.” error in SQL Server Management Studio

I get this error when trying to update some rows from SQL Server Management Studio v18.5: Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch Start Line 0] To many rows being processed ...
0
votes
1answer
26 views

Create ORACLE TRIGGER which (INSERT OR UPDATE) in TABLE1 whenever a new INSERT is done in TABLE2

i'm trying to create a TRIGGER for my Oracle database. I Have 2 tables in it(table1 , table2) Table 1 has 3 columns (id, name, value). id can be non unique here Table 2 has 2 columns ( id, concat) id ...
6
votes
2answers
5k views

triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables. Declare @NewID varchar(50) = Replace(...
-1
votes
0answers
37 views

CASE WITH NEW. AND OLD [duplicate]

New problem. from : this task NEED to do 4 state CASE with using OLD. and NEW. value using funcion as trigger There is problem with update only with state WHEN OLD=NEW This is main part of code WHEN '...
0
votes
0answers
21 views

DB design for single Dependent table column to multiple Primary table

I have a scenario where records in the dependant table(say History) are created by two entities, one User and other Application. For which my purposed design is User ----------------- UserId (PK) ...
0
votes
1answer
32 views

How to automatically increment several Integer columns based on alphabetically ordered text columns?

Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in ...
0
votes
2answers
6k views

How to create an insert trigger?

This is 1/4 of an overall assignment and I need help with this part. The assignment question: Create a trigger on the Rental table that will check when a new rental is to be inserted, that the member ...
0
votes
0answers
12 views

Catch contraint error inside trigger on the table being inserted/updated

I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452) Finding an answer to that is not ...
0
votes
1answer
271 views

Use of MySQL trigger to insert row's week number

I have a table of orders in a MariaDB database that currently has a datetime column. For the dashboard I am building for this database, I have a statistic I would like to display which is "Orders This ...
1
vote
0answers
23 views

InnoDB trigger deadlocks itself?

I have a MySQL 5.7.22 database using the InnoDB engine. There's a table respondents that has 6 associated triggers - three of them make CRUD operations on a completed_respondents table, and the other ...
0
votes
0answers
23 views

In ORACLE 11g , triggers, how i can get EXCEPTIONS, BEFORE they can be trigged

I had a package, very extensive who cannot be modified nor debugged. So i wrote an trigger then to get the table info before the process crash, BUT, in testing of the trigger as i insert in the table, ...
3
votes
1answer
6k views

mysql trigger update after insert

I need help to create a trigger that can calculate prices after promotions. I have two tables "product" and "promotion". after inserting in promotion table the promotion price and the product id, I ...
0
votes
1answer
15 views

Update main and second table with a before update trigger for message logging

I currently have the following testing schema: CREATE TABLE userMessage ( message_id BIGSERIAL PRIMARY KEY, creator_id BIGINT NOT NULL REFERENCES "User" (id), created_at BIGINT ...
0
votes
1answer
87 views

Mariadb - Error declaring variable in trigger

I'm trying to declare a variable to use later in my trigger, but I'm getting this error. ERROR 1064 (42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB ...
0
votes
1answer
23 views

Creating trigger from one database table to another database table postgresql

I have two database say db1 and db2. And two tables user_auth_table in db1 and user_table in db2. Now I want to trigger a function which sync up few values in both tables for each new row or on each ...
1
vote
0answers
13 views

is there any method should i use for trigger update?

I try to modify the syntax of SQL trigger to mysql trigger [check 32:01 for reference] ]1 someone suggested me to use this syntax in mysql, however I got error in mysql. DELIMITER // CREATE TRIGGER ...
1
vote
1answer
754 views

Deleting old rows when a new one is added and there are more than 100 rows

For a school assignment we need to keep the number of rows in a mysqldb under 100. The newest rows must stay. I've tried using a trigger but with no success. CREATE TRIGGER delete_rows AFTER INSERT ...
0
votes
0answers
12 views

PostgreSQL Trigger IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column ...
1
vote
1answer
587 views

MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions?

I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB). My solution (do not work) was to add a AFTER TRIGGER ...
0
votes
1answer
22 views

Throw an error from the trigger?

When trigger returns NULL, row is not inserted, but result of the operation is still reported as successful (INSERT 0 0). And in my opinion this is not logical, as one would probably want to handle ...
0
votes
1answer
584 views

Before Insert Trigger not firing

DELIMITER $$ CREATE TRIGGER `merchant_insert` BEFORE INSERT ON `merchant_table` FOR EACH ROW BEGIN INSERT INTO merchant_seq VALUES (NULL); SET NEW.merchant_id = CONCAT('MC', LPAD(LAST_INSERT_ID(),...
1
vote
1answer
277 views

Is it possible to run a trigger on Table Creation in MySQL

Simple Question. Is it possible to create a Trigger that runs everytime a Table is created. The goal is to create a metadata table for each table, but I can worry about dynamically passing the table ...
0
votes
1answer
121 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
1answer
422 views

Execution time of triggers fired when a procedure is executed in postgresql

I have a stored procedure which may delete and/or insert tuples into a database table. This table has an AFTER INSERT OR DELETE OR UPDATE trigger. Basically I want to retrieve how much time was spent ...
1
vote
1answer
762 views

Force replication to statement for INSERT to a table to fire trigger on slave

We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that ...
0
votes
1answer
56 views

Limiting the occurence of a value in PostgreSQL

Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need ...
2
votes
1answer
428 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 ...
0
votes
1answer
17 views

Triggers or views on frequently updating table

We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database ...
0
votes
1answer
902 views

Triggers that inserts values to a table from another table

I am trying to create a report table that contains drug_id, total sales and average sales from an inventories table. This code doesn't work as it should. It duplicates records and when I insert ...
3
votes
2answers
214 views

Do MySQL (5.6) triggers support 4 byte emojis?

I'm using MySQL 5.6.34 with the following settings: "character_set_client","utf8mb4" "character_set_connection","utf8mb4" "character_set_database","utf8mb4" "character_set_filesystem","utf8mb4" "...
0
votes
1answer
115 views

MYSQL, how to build this Trigger

I have two tables in mysql Table: objects 1: ID (Auto Increment) INT eg: 1 2: Object (like "Flower) TEXT eg: Flower 3: Rank (like 90, calculated) FLOAT eg: 90 ...
0
votes
1answer
46 views

Is it good practice to only delete through a trigger to enforce 'deleted by' auditing?

We've decided to design our database such that we actually delete rows when we want to delete some data, rather than just marking them as deleted, because the latter approach means that you always ...
0
votes
1answer
354 views

Create trigger to ignore user input for Identity Columns

Using the this table definition CREATE TABLE testtable ( id NUMBER GENERATED AS IDENTITY, desc VARCHAR2(30) ); I would like to create a trigger before inserts and updates to ...
0
votes
2answers
34 views

How to update data with a trigger function and a join

I have two relations : CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int); CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int); INSERT INTO ...
0
votes
0answers
47 views

Logon Trigger IP Address Issue

I have this logon trigger to restrict IP addresses. It is the same trigger I have seen everywhere. However, it is not working as expected, and I was hoping someone could give some direction. I am ...
0
votes
1answer
28 views

MySQL : Stored Procedure from Trigger giving duplicate results

I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK. When i'm ...
0
votes
2answers
3k views

Create MySQL CHILD and PARENT records at the same time and set CHILD.PARENT_ID by using MySQL Triggers

I want to create relationships between parent and child when booth doesn't exist and when new child is created. This somehow can be done with Triggers but i don't know how to work with SQL. After ...
0
votes
1answer
294 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
2answers
5k views

MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger

I have Created a trigger, where I am storing some specific data in a table and after the trigger gets executed I am deleting that Table. The query : CREATE TRIGGER `afterinsert_Golden_Ball` AFTER ...
1
vote
2answers
1k views

MySQL After Update Trigger apparently not running

I have the following After Update trigger on a MySQL table: CREATE DEFINER = 'root'@'localhost' TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD AFTER UPDATE ON ...
1
vote
1answer
2k views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
0
votes
0answers
36 views

Trigger to loop and send incremental email based on condition [duplicate]

I have a requirement in Oracle apex to implement dynamic approval workflow in issue tracker. I am trying to write a trigger for the same as below. I need to further modify it so that it runs ...
-1
votes
1answer
2k views

Trigger is not working in SQL Server 2012 on runtime? [closed]

In SQL Server 2012, I create two tables TABLE1 and TABLE2 After that, I created a trigger for TABLE1 Trigger name: PUSH_TABLE2 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo]....

1
2 3 4 5
25