Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,244
questions
-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) ...
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 ...
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
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) ...
-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
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
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
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, ...
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
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 ...
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 ...
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 ...
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 ...
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
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 ...
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
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
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
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
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
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 ...
0
votes
1answer
30 views
only last row is inserting in table when having “instead of insert” trigger on that table [duplicate]
I have this trigger
CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test]
instead of insert
AS
begin
declare @name nvarchar (50),@city nvarchar (11),
@tdate datetime , @fid int,@fopd datetime;
select @...
0
votes
0answers
14 views
Create mysql update trigger where another table
i have vehicule table with kilometres column and status .
And mp table with km column
I want to create trigger that updates the status based on the km column from mp table.
If vehicule.kilometres ...
0
votes
1answer
32 views
Are having TRIGGERs for every upvote/downvote detrimental in terms of performance?
I plan to have three tables: Users, User_Reputation and Voting_Records. The Users table would hold the primary key id for the registered users, along with personal information such as email and ...
0
votes
1answer
24 views
How to pass a newly INSERTed row to a function?
I have following tables:
table1: table2:
| uid | did | | uid | did |
|-----+-----| |-----+-----|
| 1 | 2 | | '1' | 2 |
| 4 | 2 | | '4' | 2 |
| 2 | 3 | | '2' | 3 |
| 1 | 8 ...
0
votes
0answers
17 views
PostgreSQL - problem with INSERT trigger enabled when performing COPY FROM STDIN bulk insert (hangs/locks)
So I work with a database that regularly imports a significantly amount of records- on the orders of millions. To do this the most efficient way possible, I use the PostgreSQL COPY FROM method, which ...
0
votes
1answer
32 views
Logon failed for login due to trigger execution / LOGON TRIGGER
I have this situation.
I created a logon script to block any session for specific logins. These logins are in a table.
So the behaviour of my script is simple.
Get the login from the table, compare it ...
0
votes
2answers
22 views
Can we prevent table from update directly but allow update using trigger from another table?
So, let's say I have 3 tables:
stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty
What I want to do:...
2
votes
1answer
40 views
Can I update old data and insert new data if not exists currently in a single query
I was trying to wrap my head around how to go about doing an update to a table if an updated list of the same table type does not have one or more of its original rows. The list of services comes from ...
0
votes
0answers
41 views
Cascading Triggers
I'm running MariaDB 10.2.31 on Ubuntu 18.04.4 LTS. My data consists 99% of JSON-encoded strings from a remote API.
Example (orders) very much simplified:
{
"id":123,
"referrer": "webshop",
...
1
vote
1answer
52 views
Set var=“NEW.this_word_column” in a trigger function if “word” is present in a column name
Context
I'm using PostgreSQL 10.12.
I want to be able to fire a trigger function on some specific tables with geographic data.
This trigger function is designed to create a PotGIS point into a ...
0
votes
1answer
51 views
PostGIS trigger is fired without error, but the action seems not executed
Context
I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points.
This trigger must compute a new geometry for each inserted or updated point. This new geometry must be ...
0
votes
2answers
44 views
Dynamic auditting trigger without temporary tables
We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows. In order to use the INSERTED and DELETED tables in ...
0
votes
1answer
18 views
update all rows after insertion in mysql
I am working with mysql and I have a table with the following structure (a summary):
CREATE TABLE `costs` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`utility` DECIMAL(9,2) UNSIGNED NOT NULL,
`...
0
votes
0answers
21 views
Sql service broker and SqlDependency
I have a system where my TCP/IP command prompt application receive data and insert that data in [database1].[mytable].
Now I want to move that data from [database1].[mytable] to [database2].[...
0
votes
0answers
14 views
cannot create trigger without super privilege even though bin_log is off
I'm trying to import a magento 2 database dump to a MariaDB 10.1 server however it's asking me to provide SUPER privilege in order to finish the import.
It fails with the message
ERROR 1227 (42000) ...
0
votes
0answers
22 views
MySQL - Check out trigger not executing
Im implementing a trigger to be executed when the end date of a booking is passed
Its supposed to change a cats status within checked in from 1 to 0
when updating the table changing an ex booking ...
0
votes
1answer
31 views
Slow Trigger performance when big batches (take2) [duplicate]
take2 of this post since it was missing information and it was closed prior to the update that I added.
I have an update trigger that inserts into auditing tables. We had no problem until someone ...
0
votes
1answer
36 views
Why doesn't my trigger put data into my table?
When I execute my trigger, it says it executed commands, but there is still no data in the tAttackPlan where it is supposed to insert data into. Can not figure out why.
SET ANSI_NULLS ON
GO
SET ...
0
votes
1answer
20 views
use two condition in a trigger [closed]
I have 3 tables (shipment, Supplier, Part).
create table Shipment (
SID int,
PID int,
Qty int,
);
create table Supplier (
SID int,
Sname nvarchar(10),
Status int,
);
...
0
votes
0answers
16 views
Mysql replication trigger error
I have replication master - slave on two different machines (both centos 7, mariadb 5.5).
When on master i run query which fires some trigger i've got error in Master's logs.
[Warning] Unsafe ...
0
votes
1answer
10 views
SELECT inside trigger gives wrong result (it ignores the WHERE clause)
When I run this code inside an after UPDATE trigger:
SELECT * FROM `stock_totals` WHERE `product_id` = 2 AND `stock_place_id` = 3 INTO OUTFILE 'query3.csv'
FIELDS TERMINATED BY ','
...
0
votes
2answers
65 views
Slow Trigger performance when big batches
I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when ...
-2
votes
1answer
68 views
MySQL trigger calling stored procedure always get null value for out parameter of stored procedure [closed]
My stored procedure OUT parameter, always return a null value.
Here is sample Table, Trigger and Procedure code.
Table: test
Columns:
id - Int
status - enum(‘pass’, ‘fail’)
status - enum(‘pass’, ‘...
2
votes
1answer
69 views
SQL Server-Table has many AFTER INSERT triggers that insert into the table itself
I purposely created this example to represent my problem.
I created table like this:
CREATE TABLE a
(
id INT
)
I then created 2 AFTER TRIGGER like this:
CREATE TRIGGER insert_a
ON a AFTER INSERT
AS
...
2
votes
1answer
39 views
ROLLBACK make data in INSERTED table is removed in AFTER INSERT TRIGGER
I give an example to show my problem. I create a table as the following:
CREATE TABLE a
(
id INT
)
I create a AFTER INSERT trigger to don't allow to insert id=1 into table "a"
CREATE TRIGGER [dbo]...
1
vote
0answers
25 views
data-modifying CTEs and triggers
If a data-modifying CTE inserts into two tables t1 and t2 that both have triggers, is there any guarantee on the order in which statement-level triggers are run?
-- t1 and t2 both have insert ...
0
votes
1answer
38 views
How to deny viewing definition of server level trigger?
I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc.
I've tried 2 options:
DENY VIEW ANY DEFINITION ...
