Questions tagged [trigger]
Procedural code automatically executed in response to a database event.
0
votes
1answer
56 views
How to make a trigger for create_view
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
42 views
SQL Trigger not respecting where clause
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 ...
-4
votes
0answers
21 views
First gamer who installed a game [on hold]
!) How to identify who are the gamers who has first installed from the activity table but there is no action called install there?
the table contains date ,activity like play, user id and time stamp
-1
votes
1answer
32 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
40 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
56 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 ...
-1
votes
0answers
30 views
MySQL Trigger [stock levels] [closed]
I need to create a trigger to record current stock levels for each of my products.
I have a product table with quality, product name and an orderitem table which contains qualities of orders.
Do I ...
0
votes
1answer
17 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
30 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
42 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
18 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
2answers
93 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
22 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
50 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
27 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
37 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 ...
0
votes
0answers
30 views
Trigger Resource Consumption
I made a trigger that activates every time an insert, update or delete has been made on a certain table. I have already implemented it into the tables in one of our servers.
I now need to know if ...
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
27 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 ...
0
votes
0answers
28 views
Trigger Name Blank in sys.dm_exec_trigger_stats
I'm trying to see the triggers that are in my server, I use the query below. It's able to get me the list of triggers I have in the database, but the "trigger name" somehow ends up with column somehow ...
1
vote
1answer
309 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
81 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
62 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
0answers
27 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
25 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
37 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
293 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
24 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
130 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
27 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
33 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
55 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
65 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
34 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
111 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 ...
1
vote
1answer
33 views
Automating Redshift data dump to a PostgreSQL DB every hour
GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I ...
0
votes
0answers
14 views
Get the syntax that fired an insert trigger [duplicate]
Is there a way for an insert trigger to get the line that caused it to activate?
0
votes
2answers
53 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 ...
0
votes
1answer
377 views
Trigger to check if a column is updated and another one has a specific value
I'm writing a trigger for a SQL Server database. What I want is, when a table is updated, check if a specific column is updated or not and if another column has a value = 'W' or not. If it is so, the ...
0
votes
0answers
40 views
Creating a trigger on a column either deleting row or updating row on update, Oracle
I haven't had any experience with triggers, however I'm trying to make a trigger, but i'm making a change in our database setup so that we will follow GDBR regulations.
For this I'm required to make ...
0
votes
2answers
39 views
Mysql Trigger Insert row Another Table using by Barcode
I Have 3 Tables:
1) CREATE TABLE `UrunPaketDetay`(
`UrunPaketDetay` int NOT NULL AUTO_INCREMENT,
`UrunPaketNo` int NULL,
`Tarih` date NULL,
`Barkod` varchar(14) NOT NULL,
`...
3
votes
2answers
772 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 ...
1
vote
1answer
27 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 ...
2
votes
2answers
51 views
Deadlocks With Trigger Based Replication
A table ORIGINAL exists with the following structure:
ID VARCHAR Length 10 (key)
VALUE VARCHAR Length 10 (non-key)
A table REPLICATION exists with the following structure:
ID ...
2
votes
2answers
42 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"
"...
10
votes
1answer
901 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 ...
2
votes
1answer
33 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;
...
1
vote
3answers
54 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 ...
7
votes
1answer
371 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 ...
