Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
1,511
questions
1
vote
1
answer
135
views
oracle: deny naked sql statements
I'm looking for a solution in oracle 12c to deny INSERT, UPDATE and DELETE statements on tables.
I would only provide SELECT privileges and exec rights to execute procedures which INSERT, UPDATE or ...
- 8,726
0
votes
1
answer
4k
views
How to execute AFTER INSERT TRIGGER with an auto-incremented (seq.NEXTVAL) column value in Oracle PL/SQL?
Below is my AFTER INSERT TRIGGER in Microsoft SQL Server:
CREATE TRIGGER new_airline
ON Airline
AFTER INSERT
AS
BEGIN
DECLARE @Airline_Name VARCHAR(50), @Country VARCHAR(50)
...
CommunityBot
- 1
1
vote
2
answers
4k
views
DELETE inside INSERT Trigger
I wanted to make rotation inside my logs table to store last 100 rows only, and decided to use triggers, as in
create trigger rotate_log
after insert on logs
for each row begin
declare
cnt int;
...
- 4,829
1
vote
2
answers
367
views
How do I rewrite the below Oracle trigger so it will work on SQL Server?
I am migrating from Oracle to SQL Server using SSMA and I get thrown an unparsed SQL error on the below code. Can anyone help me convert this to T-SQL? Thanks in advance.
CREATE OR REPLACE TRIGGER "...
- 4,829
0
votes
1
answer
2k
views
Trigger after insert, insert new row into another table [closed]
I'm developing a Wordpress site.
I'm trying to insert a new row into a table when new row with specific value in usermeta table is added.
This is what I got for now:
DELIMITER |
CREATE TRIGGER ...
- 11
1
vote
2
answers
907
views
PostgreSQL - how to recognize if function was called by delete or update trigger?
I have function, which should be called after update and delete on one table.
At the end of function I have to return "old" if function was called after delete or "new" if procedure was called after ...
0
votes
2
answers
129
views
Update on cascade
I have two tables:
service (id_service, libel, status)
agent (id_agent, name, surname, id_service, status).
The status field values present in both tables have only two values 0 or 1 (0 = ...
- 3
4
votes
1
answer
941
views
How can I write an "on-update" trigger which takes a table-name as argument?
NOTE: The following example is a strongly simplified code, but it illustrates the task in a reproducible example. Looking at this example may make you think "why do it like this"? In reality, the full ...
- 741
3
votes
1
answer
2k
views
Is it possible to execute an "update" trigger if all columns except one have changed?
Assume I have the following table:
CREATE TABLE data (
key TEXT primary key,
some_interesting_value TEXT,
inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated TIMESTAMP ...
- 9,010
15
votes
3
answers
21k
views
Passing info on who deleted record onto a Delete trigger
In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic.
I can track Inserts/Updates by ...
- 68.8k
0
votes
2
answers
2k
views
creating a trigger after update exclude a column?
I want to create a trigger that set a column to to_sync true if the other columns has been changed.
CREATE TRIGGER [dbo].[product_trig]
ON dbo.product
AFTER UPDATE
SET NOCOUNT ON;
IF NOT ...
- 3,043
6
votes
2
answers
11k
views
"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?
For tables I can implement "if not exists" and "if exists" as folows:
--if table exists - drop
If OBJECT_ID('A','U') is not null
Drop Table [A]
--if table not exists - Create
If OBJECT_ID('A','U') is ...
- 3,306
1
vote
1
answer
1k
views
Creating a Trigger to Update Matching Records in Another Table
Morning, All -
I'm currently working on some SQL logic, and wanted to get some input on it.
What I'm trying to accomplish is the following (Psuedo-code):
When a record is inserted into Table A -
IF ...
- 11.6k
-2
votes
1
answer
102
views
Simple Trigger Is Not Created in Mysql
I'm creating a trigger in mysql, but even with the correct syntax and the query being accepted the trigger is not created. If anyone can help. The following is the sql:
DELIMITER $
CREATE TRIGGER ...
- 78.1k
12
votes
1
answer
4k
views
Trigger firing despite no rows being affected
This is more of a general question but the motivation for this question was a problem I faced while using SQL Server.
I have this trigger attached to an Insert event on a table that contains some ...
- 84.1k
2
votes
1
answer
27k
views
find and insert row to another table using mysql trigger
I have the following three tables in mysql database named My_Company
mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | ...
2
votes
1
answer
56
views
PostgreSQL insert record by trigger [closed]
create table tab1(id int,name text);
create table tab2(id int,name text);
create or replace function t_tab() returns trigger
as
$$
begin
insert into tab2
values (new.id, new.name)
return new;
...
- 3,573
3
votes
3
answers
2k
views
How to get the definition of a database trigger?
when I run the following script in one of my databases:
SELECT * FROM SYS.triggers
when I try to find out about sys.triggers
Contains a row for each object that is a trigger, with a type of TR ...
- 16.2k
7
votes
1
answer
1k
views
T-SQL trigger to update columns
I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Trigger to cascade updates to a second table
In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified.
Function:
CREATE OR REPLACE FUNCTION schema.maj_tbl2()
RETURNS trigger AS
$BODY$
...
- 176k
-1
votes
1
answer
770
views
Trigger to copy deleted rows to another identical table with Cascade Delete On
I have two tables.
Sale and SaleProducts
SaleProducts contains all the products that have a Sale.
1 sale, many products. (1 to many)
SaleProducts has a relation to Sale with On Delete set to ...
- 19.9k
0
votes
1
answer
3k
views
Can't create triggers in MySQL, no errors but no triggers either [closed]
I'm trying to create triggers without much success I'm not getting any error but the triggers don't show up either... So what's wrong with this?
USE `wa3`;
CREATE TABLE `transactions` (
`id` ...
- 4,348
2
votes
3
answers
2k
views
pt-online-schema-change trigger problem
I need to run an alter with Percona tool but it gives error:
The table gts.xml_bookings has triggers. This tool needs to create its own triggers, so the table cannot already have triggers
So I ...
CommunityBot
- 1
3
votes
1
answer
5k
views
Alternative to Dynamic SQL inside trigger?
My database has the following tables:
TAG
----------------------
| tag_id | tag_name |
----------------------
TAG_VALUE: Stores values associated to each tag
----------------...
- 17.7k
6
votes
3
answers
12k
views
SQL Server: update trigger fires before insert trigger
I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to ...
4
votes
3
answers
1k
views
Will this trigger significantly degrade performance?
I have this trigger to (do my best) to prevent brute forcing logins. It has two seperate functions:
If a failed login has been attempted more than 4 times (so read that exactly 5 times) from the same ...
CommunityBot
- 1
3
votes
1
answer
4k
views
How to create `instead of` trigger on a table in postgres?
I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if ...
CommunityBot
- 1
2
votes
0
answers
87
views
Is there a setting in SQL Server to keep triggers from firing SELECT @@IDENTITY
This is a SQL Server 2014 Server and we have application code the does a Select @@Scope_Identity after an insert into a table.
Recently other developers added triggers to this table and ever since, ...
CommunityBot
- 1
1
vote
1
answer
938
views
How to check with update trigger if only one column was updated postgresql
I have a table with many columns and one special column that indicates if is synchronized or not. When an update occurs in the other columns I processed and update a 'log' table, this updates comes ...
- 1,352
7
votes
1
answer
88
views
What can be the purpose of this trigger?
I stumble on this trigger when trying to analyze some DB:
Create trigger [tbl_Details_Trigger] on [tbl_Details]
Instead of Insert
As
Insert into [tbl_Details]
Select * from ...
- 70k
6
votes
2
answers
12k
views
How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?
Solution in MS SQL
MS SQL trigger functions have deleted and inserted system tables in which all rows affected by operation are stored. You can count updated rows:
set @updatedCount = (select count(*...
- 513
6
votes
2
answers
9k
views
SQL Server: trigger for insert update and delete caught when no data been changed?
I've added a simple table to a db called: aaa_log with columns :(id ,name, op))
CREATE TABLE aaa_log (
[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[name] [varchar](50) NOT NULL,
[op] [...
2
votes
1
answer
8k
views
RAISERROR on a trigger. What will happen? Will we get informed?
What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below?
RAISERROR('Attempt to modify supposedly immutable number.',16,1)
How will we know this ...
CommunityBot
- 1
1
vote
1
answer
2k
views
MySQL / PostgreSQL: how to call a script when a row inserted / updated
Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).
Every second multiple rows are being inserted or updated on the first DB. For each ...
CommunityBot
- 1
0
votes
1
answer
5k
views
Update value in table based on condition in other table with trigger
I have 2 tables, first stores the details of all the races 1: and second the users who have joined which races 2:
I want to update the race_status to Full and record_status to 2 in race table, if ...
- 19.9k
0
votes
1
answer
1k
views
How to ensure that row with same PK inserted replaces old row in MS SQL Server, instead of throwing exception of PK Violation?
I'm transferring bulk data from a C# DataTable into a SQL Server DB. The DataTable provides values for all 26 columns except the first column, which is an auto-incremental ID The schema skeleton is as ...
- 1,352
-2
votes
1
answer
2k
views
oracle to postgresql syntax convert
oracle :
CREATE OR REPLACE TRIGGER ORA_ADMIN.T_INCOMING_FILE
BEFORE INSERT ON ORA_ADMIN.INCOMING_FILE FOR EACH ROW
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
SELECT incoming_file_seq....
- 1,930
1
vote
1
answer
29k
views
SQL Error [42883] - function does not exist - Cannot call function from trigger
I want to insert a new row to Table2 when Table 1 is inserted a row. I have created a function for that. However, when I try to create the trigger, it gives an error although function is created and I ...
- 9,323
1
vote
1
answer
87
views
Suppress bogus warning for query using triggers
As far as I understand based on my research, a very simple update query like:
UPDATE someTable SET status = 10 WHERE id=123;
will fill the error log with bogus warnings if the query invokes triggers,...
- 23
0
votes
1
answer
1k
views
when a table trigger fires, what user does the trigger execute as
I have a table that I am using Insert/Update/Delete triggers to audit. I want to restrict access to the audit table so that only the triggers can write to it. What I am trying to find out is, what ...
- 28.2k
1
vote
2
answers
1k
views
Trigger To Find Deleting Job Agent History
We are trying to debug why SQL Agent Job history is getting deleted every 8 hours. In reading the following article, it suggests many great ideas like:
Create a trigger to check what application is ...
- 539
1
vote
2
answers
2k
views
Logging the Execution times of dynamically constructed mysql stored procedures
I have a MySQL Server.
The MySQL server contains a huge network data collection.
The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
4
votes
1
answer
3k
views
Triggers vs Views in MySQL
We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...
CommunityBot
- 1
1
vote
0
answers
287
views
Entry inside audit log table for CUD operations on tables
I have a DB e.g "dbAuditLogs" inside created tables like as mstUser,mstEmployee,mstDepartment and auditHistory. when any CUD operation performed on mstUser/mstEmployee/mstDepartment then want to ...
- 19.9k
4
votes
3
answers
2k
views
Win-Loss Record in SQL Server
I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.
I'm building two tables fight and fighter. The fighter table ...
CommunityBot
- 1
1
vote
1
answer
4k
views
"Logon failed for login 'sa' due to trigger execution." When No SA Login Triggers Defined
Last night, I received some error messages in my SQL Server instance stating:
"Logon failed for login 'sa' due to trigger execution."
The instance has a number of logon triggers enabled, but after ...
- 46.5k
1
vote
2
answers
115
views
Alternative to second column for tracking a FOREIGN KEY pre-deletion
Let's say I have a table A, that has one column: just an ID. This is obviously the primary key.
Next, I have a second table, which has an ID for table A, and then a second ID. The primary key is on ...
- 3,573
0
votes
1
answer
65
views
Restrict the occurrence of a value according to the respective value in another column in a PostgreSQL table
I'm using a PostgreSQL table to link taxonomic names with the respective taxon concept as follows:
create type name_status as enum ('accepted','basionym','synonym');
create table temp (
concept_id ...
- 97.9k
0
votes
1
answer
62
views
Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?
Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?
In my case, I am using a trigger to read from two view tables (Decisions2 ...
2
votes
1
answer
66
views
How to export base and discover its structure
We are with a small problem at work. Our DBA decided to go around the world on a journey of self-discovery. I really love the idea and wish the best for him. The only detail is that he left without ...
- 3,540