Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
0
votes
0answers
17 views
DDLTrigger_Audit error “The user does not have permission to perform this action”
I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error.
database "XXX" failed on Jan 17 2019 ...
-1
votes
1answer
34 views
Can I INSERT the result from a query INTO a new table with a TRIGGER?
I have a table named Stadistics which contains the data shown below:
date hour num_prodcuts
--------------------------------
2019-01-16 12:42 3.2
2019-01-17 13:40 ...
1
vote
1answer
14 views
Synonym being recreated randomly - possible to create some kind of trigger/log?
We have a couple of databases with synonyms to link each other, one of the synonyms is randomly being recreate incorrectly with an additional set of square brackets in the database name. I've checked ...
0
votes
1answer
45 views
Ensuring unique value for group of rows in table with trigger and concurrent transactions
I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
-1
votes
2answers
28 views
mysql trigger data from a many to many relation
I am new to triggers, so I want to know how I could select attributes from 3 different tables to insert them into a new table.
I have 3 tables with a many to many relationship:
table users wich has ...
0
votes
1answer
36 views
How can i Update the status of field with a trigger?
This is my example code:
create trigger MyTrigger on myTable
after update
as
begin
set nocount on;
update talleres set status='active' where FirstDate=getdate()
end
go
create trigger MyTrigge2 ...
0
votes
1answer
47 views
A trigger making a timestamp of when another column was changed
I've beeen trying to create a trigger, that'll make a timestamp, of when our account was "deleted" which we do by setting deleted = 1. Below code creates a trigger for that purporse, however it has ...
2
votes
2answers
45 views
SQL Server 2016 Trigger issue
I need a little help with a trigger.
Where I'm in wrong:
CREATE TRIGGER [dbo].[tr_ins_MyTriggerName]
ON [dbo].MyTableName
FOR INSERT
AS
DECLARE
@Number nvarchar(20) ,
@Date datetime ,
...
0
votes
0answers
29 views
SQL Server 2014 Triggers and Merge Replication
I am trying to have a trigger fire when the Merge agent\user insert\delete\update data.
The trigger inserts data into a log table.
I don't want to use NFR (Not For Replication) because I need that the ...
1
vote
1answer
22 views
Trigger error in MySQL
I'm using Toad for MySQL, and I have to make a trigger so that:
When the rent of a car is <=15 and >= 25, I insert a letter "A" in the field category
When the rent is > 25 and <= 35, insert the ...
1
vote
0answers
15 views
Azure Database for PostgreSQL and Event Triggers
I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as ...
1
vote
1answer
33 views
Triggers corrupt merge replication
I have merge replication (SQL server 2014) and a table that have 3 triggers that monitor delete, insert and update.
All of the triggers are after trigger not instead of.
I'm updating the value on one ...
1
vote
0answers
55 views
Trigger for add discount on total price calculated from other table - oracle 11g
I have the following schema:
CREATE TABLE ORDER(
OrderId NUMBER PRIMARY KEY,
Value NUMBER);
CREATE TABLE ORDERITEM(
OrderItemId NUMBER PRIMARY KEY,
OrderId NUMBER,
...
2
votes
1answer
101 views
SQL Server : delete row trigger
I have these 3 tables:
ConstructorEmployee
EID (employee ID) as primary key and other key.
Project
PID as primary key and other keys.
ProjectConstructorEmployee
PID and EID as foreign keys to ...
0
votes
1answer
63 views
Accidental deletes at SQL Server Subscriber
Unfortunately, this happened twice in 2 weeks on our most critical db server; just before Christmas! Instead of running a delete script at the publisher, the script was run at the subscriber, which ...
2
votes
1answer
26 views
Postgres triggers for group_id
I have a table that looks like this:
CREATE TABLE transactions(
id SERIAL NOT NULL PRIMARY KEY,
value NUMERIC(10,3),
note TEXT,
group_id INTEGER);
I'd like to update the group ID automatically with ...
3
votes
1answer
46 views
Database-wide trigger
I'm new to DBA and I'm attempting to create a trigger that will update the a specified column in one table whenever an insert is made into every table across the database.
I understand it is possible ...
0
votes
1answer
49 views
Stored Procedure infinite looping after index updates
I have inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was ...
0
votes
1answer
63 views
How to make a trigger for create_view [closed]
I need to make a trigger about who and when created a view. I have some groundwork about it.. So, when some user is running a script for create view, trigger is crushing because this user doesn't have ...
0
votes
1answer
45 views
SQL Trigger not respecting where clause [closed]
I have a trigger:
CREATE TRIGGER [dbo].[UpdateTbl]
ON [dbo].[TableB]
AFTER INSERT
AS
BEGIN
DECLARE @role varchar(20)
select @role = u.[role] from inserted i join users u on ...
-1
votes
1answer
46 views
Receiving error about ambiguous field, but there is only 1 table being referenced
My understanding of ambiguous fields, or where I see the error elsewhere, is when I've defined a JOIN on multiple records and failed to specifically say which table has the field when the field names ...
0
votes
1answer
41 views
Checking update value before update
First time posting so apologies if this has been asked already.
I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come ...
0
votes
1answer
58 views
MySQL server 5.1 trigger problem
I have implemented a trigger. But once I try to update the table it is giving me the error:
can't update table 'booking' in stored function/trigger because it is already used by the statement which ...
0
votes
1answer
19 views
Enforce functional dependencies by using triggers in SQL99
The question is as below:
For R(A, B, C, D, E), write a trigger to enforce AB->C on insert.
This is my solution:
CREATE TRIGGER fdEnforceInsert
BEFORE INSERT ON R
REFERENCING NEW ROW AS N
FOR EACH ...
0
votes
2answers
48 views
INSERT AFTER trigger failing with multiple rows
I have a trigger (AFTER INSERT) that has been working in single inserts, and I believed it would work for multiples but its not. I researched it and am currently stuck as what I'm reading implies it ...
0
votes
1answer
45 views
INSERT trigger not SELECTing rows
I'm working with a application and I have no control over the application source code, only SQL.
The entire process is this:
Row inserted via an application
Vendor trigger fires and calls an SP to ...
1
vote
1answer
22 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
3answers
167 views
Synchronize two MySQL databases between two servers
Is there any way to connect a database on two servers inside a trigger?
I have 2 databases on 2 servers. The first server is the primary, and the second server is the secondary. I want to sync both ...
0
votes
1answer
43 views
not-yet-assigned record is indeterminate, but triggers on update (postgresql 9.3)
I create a very simple function, ensuring it only happens with an update.
CREATE OR REPLACE FUNCTION simple_trigger() RETURNS trigger AS $$
DECLARE
_unused_variable INTEGER; -- unrelated ...
3
votes
1answer
59 views
Generating a generic INSERT/UPDATE Trigger to conform data?
I am trying to generate triggers on a number of tables to force data to upper-case and to trim leading and trailing spaces when data is inserted or updated. The issue I am having is determining in ...
1
vote
1answer
31 views
Having troubles creating a specific TRIGGER in MySQL
So I am trying to implement a trigger in MySQL that is triggered once a new user is registered.
The relation of my tables are:
USER_ACCOUNTS has primary key USER_ID.
RACE_TRACKS has primary key ...
0
votes
2answers
52 views
MySQL trigger/check on INSERT based on multiple factors (location, date and time)
I have a shift schedule planner for various warehouses. I'm trying to add multiple checks at DB level. If I'm using a composite key (by removing shift_id) to match staff, location,date time it's works ...
1
vote
2answers
34 views
SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?
I have a table named _Test which has the following values stored in it.
Primary Key : Id
I also have designed a trigger on that table
ALTER Trigger [dbo].[_Test_Data_Save]
On [dbo].[_Test]
For ...
0
votes
1answer
33 views
MySQL 8.0 Triggers Current_User not working
I have this trigger that inserts a row to a table
CREATE
DEFINER=`root`@`localhost`
TRIGGER `user_log_AFTER_INSERT`
AFTER INSERT ON `user_log`
FOR EACH ROW
BEGIN
INSERT INTO ...
1
vote
1answer
857 views
How to create an INSERT/UPDATE/DELETE trigger
My knowledge with triggers is very poor.
I want to create an INSERT/UPDATE/DELETE trigger on a tableA with column names (A1,A2,A3,A4,A5,A6). After the triggers fires, I want to put that data in ...
1
vote
3answers
88 views
How can a trigger be bypassed?
Assuming I have the following table:
Create Table [dbo].[Test_IP]
(
[IP] varchar(40),
[IPType] varchar(6)
)
There are more triggers on this table, but all of them are AFTER triggers, and it is ...
1
vote
1answer
105 views
Insert Trigger to a Linked Server error
I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the ...
1
vote
1answer
41 views
SQL DB Trigger - mutiple column changes
I need to trigger off 17 different columns and email the change from what it was to what it is changed to. The trigger below works but I feel like this is not the correct way to achieve this. Also, ...
1
vote
1answer
26 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 ...
0
votes
2answers
45 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;
...
1
vote
2answers
295 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 "...
0
votes
1answer
28 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 ...
0
votes
2answers
27 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 ...
1
vote
1answer
145 views
Trigger with a Variable Select and Order by
Is there a way to a set trigger that will activate once a new table is created and will get the new tables name?
I was able to come up with the query below but it only gets the first query by ...
1
vote
1answer
29 views
How do I use “OLD” or “NEW” in a dynamic SQL statement in an on-update trigger?
Assuming I have the following function being executed on a row-update:
CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
sql TEXT;
BEGIN
sql := 'INSERT INTO ...
2
votes
1answer
34 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 ...
1
vote
2answers
69 views
Trigger - If time between?
I wish to use a mysql trigger that will update a column called peak to 0 or 1 based on if the CURTIME() is between 08:01 & 11:59.
Can anyone help with this trigger?
2
votes
1answer
71 views
Monitor source SQL code of inserts into table
I have a table with rows which should not be there, means the data is wrong according to business logic. And there is a lot of possible sources the insert statement of the "bad" row is coming from. I'...
0
votes
1answer
47 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 ...
0
votes
2answers
220 views
How to use variable into SQL in MySQL trigger
I'm trying to write trigger.
I have encountered an one problem.
My Code is like.
DELIMITER //
CREATE TRIGGER upd_check BEFORE UPDATE ON user
FOR EACH ROW
BEGIN
DECLARE tempVar text;
IF(OLD.name ...
