Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

0
votes
2answers
523 views

Recursive update for tree structure in PostgreSQL

I have the following structure of table. id chNum parentid --- ------ --------- 1 1 NULL 2 1.1 1 3 1.1.1 2 4 2 ...
0
votes
1answer
2k views

Uncommitable T-SQL transaction after trigger applied to table

Good morning, I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record ...
1
vote
1answer
450 views

Server Wide DDL Trigger Permissions Issue

I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here The trigger will log the DDL ...
1
vote
1answer
236 views

Slow query and Insert with trigger

I have vehicle tracking system application based on PHP, SQL Server 2008 R2 Enterprise Edition with XEON HP Z800 Server. My database is growing day by day in millions and it makes it very slow to ...
4
votes
2answers
1k views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
1
vote
1answer
7k views

How to structure IF condition in MySQL trigger?

I am trying to write a MySQL trigger. I have two tables like this: Table A------------------------------Table B order_id--------sku---------------order_id----order_#----sku_copy ...
1
vote
1answer
498 views

Disabling triggers in stored procedures

I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of ...
2
votes
2answers
246 views

Trigger update does not seem to finish

I'm new to PostgreSQL triggers & functions and such. I've got a table: keywords and each keyword should be unique. So when I try to insert a keyword that already exists, it should simply update ...
0
votes
1answer
101 views

Trigger in trigger or trigger before table creation

I need to create trigger inside trigger as follows: SQL> CREATE OR REPLACE TRIGGER table_account_modify AFTER CREATE ON SCHEMA 2 DECLARE 3 NAZWA_TABELI varchar(250); 4 sq0 VARCHAR2 ...
0
votes
1answer
3k views

Trigger to increment a column value after update in a column of same table

I have a table with two columns: C1 | C2 1 | 3 Whenever C1 gets updated to 0, C2 should be incremented by 1. I have developed a trigger: create trigger increment after update on T1 begin ...
1
vote
1answer
346 views

postgres+JDBC: fire trigger with non autocommit transaction

i have a jdbc client that make an two INSERT query on database whitout autocommit, and a trigger on database that fires when a insertion is done on a certain table public foo() throws MyException { ...
5
votes
1answer
241 views

How to get output from trigger on linked server

Fair warning, I know this isn't the best way to do this, but I'm trying to extend some existing code without needing to do a rewrite. Anyway, I've got two SQL Server 2012 installs, one local in my ...
4
votes
1answer
593 views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
2
votes
2answers
2k views

After update triggers that deal with multi-row updates

Currently working on an database Audit project based on triggers that are fired on update on specific tables. The triggers write the changes into a table; information written are: table name, updated ...
3
votes
4answers
1k views

SQL Server audit query that fires a trigger

Using SQL Server, does exist a way to audit from inside a trigger the sql that fires it? I need to know the SQL query that fires a trigger over a database without a profiler. Thanks
1
vote
1answer
428 views

GeomFromText in trigger has error

I have this trigger: DELIMITER $$ CREATE TRIGGER geometrytrigger BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.user_geometry=GeomFromText(NEW.user_geometry); END$$ DELIMITER ; when I insert a ...
0
votes
3answers
2k views

Auto increment value needs to be changed inside trigger

I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table. Original structure: id varchar(15), seqno int(11) auto increment Joint PK ...
-3
votes
3answers
95 views

Trigger for database [closed]

As far as I understand Trigger can be written for the table of the database. Now I want the trigger for database. The database can contain multiple table, If there is any change even in one table the ...
1
vote
1answer
435 views

Capture Data Change Vs Triggers [closed]

I am constructing a database that needs to keep track of historical data. It is a Electronic medical system. All data needs to be updated in real time so the user can see what changes happened in the ...
0
votes
4answers
8k views

Create a trigger to update table data on another Server's database

I am creating a trigger in MySQL and I need a little help. I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2. These databases have the same tables names. I want ...
3
votes
1answer
498 views

Subquery returned more than 1 value

In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table. A fully working SQL ...
1
vote
1answer
4k views

BEFORE INSERT trigger in MySQL

I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger. I get an unexpected END error. I have a table named verlof_aanvragen with a column datum (and 6 more columns). What I ...
2
votes
1answer
311 views

Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review. The challenge is that my trigger logic is stored in the same schema on which I've ...
0
votes
1answer
239 views

Stored procedure not working

I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error: ERROR 1303 (2F003): Can't create a PROCEDURE from within another ...
0
votes
1answer
862 views

updation of a column after expire of specific time in mysql

i have a table where i have a column named 'state'(int(2)) and 'modify_time' (time_stamp on update current timestamp). i have an update trigger which changes the value of state column to 0 based on ...
1
vote
1answer
268 views

Trigger not Working

I created trigger on table PENDING. Pending table has 3 columns uniqueId duration maxDuration I have another table COUNT with 2 columns req_id total Here is my trigger-- CREATE TRIGGER ...
2
votes
1answer
1k views

Trigger for updating a count of rows from a table into another table

I have these two tables: mysql> select * from orders; +-------------+---------+ | Customer_ID | Item | +-------------+---------+ | A01 | CPU | | A01 | Monitor | | A02 ...
1
vote
1answer
166 views

Postgresql - how to drop a trigger [closed]

A while ago I've created a trigger in Postgresql, update_user, which sets the update_date to now() for table users. Now I want to drop this trigger. The following queries don't work: drop trigger ...
2
votes
2answers
591 views

Federated tables and triggers

This is the scenario. Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2). I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1. ...
-1
votes
1answer
72 views

checking a folder whether required file is present at a regular interval [closed]

i have 4 folder hierarchy in a following format : Hourly Daily Weekly Monthly. In every folder i have files which is coming in a frequency based on their folder's name. Now , i have ...
0
votes
1answer
385 views

Error in trigger: table is mutating

CREATE OR REPLACE TRIGGER "TRANSACTION" before update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD for each row begin declare mportfolioname varchar2(20); ...
0
votes
1answer
1k views

Synchronise data from one table to another & vice-versa using triggers

We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases ...
1
vote
2answers
427 views

Foreign key with multiple references

I have the following three tables in mysql database named "THE_COLLEGE" mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
782 views

MySQL Replication and Triggers

I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret. The setup: There are two tables A and B. When data is written or updated in the table A, then a ...
2
votes
1answer
564 views

Testing AFTER INSERT Trigger

I have the following table: CREATE TABLE Train( Train_No integer PRIMARY KEY, Loco_No integer REFERENCES Locomotive(Loco_No), Back_Loco_No float REFERENCES Locomotive(Loco_No), ...
0
votes
1answer
192 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
1
vote
1answer
266 views

Trigger not firing when deleting a record from stored procedure MySql

Bare with me, as I'm fairly new to stored procedures / triggers. I have the following Stored Procedure: DELIMITER $$ CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
2
votes
1answer
105 views

What are the risks for logging across databases via a trigger?

I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here. On SQL Server 2005, we currently log certain table changes via triggers using ...
1
vote
1answer
2k views

How can we get table name from inside trigger?

I need to pass the table name from trigger to procedure. ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON [dbo].[Projects] AFTER INSERT AS BEGIN SET NOCOUNT ON; -- procedure call ...
1
vote
0answers
619 views

Why Oracle Database trigger can became DISABLED

We have an application, using ddl database trigger on oracle database. Trigger depends on package and several tables, package depends on user defined function. It works on different customer sites ...
1
vote
1answer
2k views

Is it possible for a trigger to update a row in a table using a value that was deleted?

I have a trigger which is updating a column in a different table after the row is inserted: mysql>delimiter $$ mysql> create trigger status after insert on table2 FOR EACH ROW BEGIN ...
0
votes
1answer
216 views

How can I modify this trigger with loops and concat?

I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name ...
1
vote
1answer
403 views

Is there a workaround for lack of support of cascade triggers with MySQL?

As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
1
vote
1answer
426 views

What would cause extra records to be inserted during a INSERT trigger

The following trigger that joins Inserted table on itself creates multiple unwanted records: CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo] AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM ...
2
votes
2answers
5k views

Execution of a Java program by a trigger.

I want to make an update trigger execute a Java program. I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a Java program. ...
0
votes
1answer
1k views

Should I disable triggers, timestamps in a slave in mysql?

We're using MySQL. We have a master that eventually will have 2 slaves. There are triggers in the db that execute when data changes in certain tables. I am wondering whether to disable the triggers in ...
0
votes
1answer
3k 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 | ...
1
vote
1answer
105 views

Determine if existing Trigger acts on field DB2

I am eventually going to be writing a trigger on a field named "user2". Currently most of the values in this field are blank, but there are some seemingly random rows with values of '00' for the ...
0
votes
1answer
1k views

What happens to a Trigger if a table is dropped and rename and recreated with same name

What happens to a Trigger if a table is dropped and rename and recreated with same name? For e.g: We have a Trigger X which is define for Table A. We have a Trigger Y which is define for Table B. ...
0
votes
2answers
971 views

mysql trigger on update with insert statement

I am trying to create a simple trigger which is handled on update operations. The trigger is checking before update if a record of an update exists, then if not it is created. DELIMITER ;; CREATE ...