Questions tagged [trigger]

Procedural code automatically executed in response to a database event.

Filter by
Sorted by
Tagged with
-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 ...

1
2 3 4 5
25