Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

1
vote
2answers
122 views

Creating a mysql trigger without hard-coding database name

Is there a way to create a trigger in mysql without hardcoding the database name? Doing a typical create trigger will stop working if I dump the database and then import it into a database of a ...
1
vote
2answers
445 views

DB2: Cannot use RUNSTATS/REORG in trigger definitions?

CREATE TRIGGER MY_TRIG AFTER INSERT ON MY_TBL FOR EACH ROW BEGIN IF SOMECONDITION THEN DELETE FROM MY_TBL WHERE PK IN (SELECT PK FROM MY_TBL WHERE SOMEOTHERCONDITION FETCH ...
4
votes
2answers
2k views

Call multiple functions from trigger?

I run PostgreSQL-9.2.4 Is it possible to call 2 functions from a trigger? Let's say I have two functions for two different tables to be executed if following trigger fires: Trigger: CREATE TRIGGER ...
3
votes
1answer
114 views

selective undoing of commited transactions in PostgreSQL

I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables. Updates are very light, reads not so heavy, but the data is extremely valuable. ...
1
vote
0answers
1k views

DDL trigger AFTER ALTER ON SCHEMA behavior

Can anyone please tell me what's going on here: CREATE TRIGGER afr_alt_trig AFTER ALTER ON SCHEMA cols ora_name_list_t; BEGIN IF (ora_dict_obj_type = 'TABLE') THEN select column_name bulk ...
0
votes
1answer
77 views

Creating a MySQL Trigger

I want to create a trigger such that whenever a user is inserted in TABLE A(userid,type) then his "userid" along with some predefined value, depending in his "type", is inserted in TABLE ...
0
votes
0answers
49 views

Should triggers be used as an extra precaution to enforce business rules? [closed]

I'm new to SQL Server, and I was wondering: how often do DBA's use triggers to enforce business logic? As long as your security is tight and no user has db_datawriter (or some other all-writing ...
-1
votes
1answer
846 views

how to log insert,delete,update queries in mysql

I want to log every insert,delete,update (successfully written) queries using mysql ? I tried following ways 1) used triggers ( but i can't get the SQL query ); 2) show processlists ( only showing ...
0
votes
1answer
640 views

Trigger After Insert is preventing insertion

I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger: ALTER ...
0
votes
0answers
173 views

Minimizing possible trigger impact

I'm trying to make sure a trigger doesn't cause trouble. (Quixotic, I know!) Is there more I can do to the trigger below to make it bullet-proof? Details: We have a huge table we don't control ...
0
votes
2answers
755 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
1
vote
2answers
8k views

MySQL Update Trigger that updates multiple columns from values in another table

I have a table PRODUCTS with columns id, name, sku, packaging_type, width, height, weight, case_count. I want a trigger to fire when packaging_type is updated. packaging_type will be an integer ...
0
votes
1answer
238 views

How to create a trigger that update the date and time field and modify of the row

I want to create trigger that will update the record. The situation is like: I have one column named CheckInDateTime and one more named Status. If the administrator is not able to update the Status ...
0
votes
1answer
132 views

Are triggers bad for created/timestamp columns?

We are in need of some better auditing on some of our MySQL tables. Specifically, we'd like to store when the row was inserted and when it was last updated. Originally, I thought DEFAULT ...
1
vote
1answer
102 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
4
votes
1answer
385 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
0
votes
2answers
632 views

Trigger to delete inserted record if it doesn't exist in another table

I have a question. I have two tables: zip_code (zip_id, zip_code, state) - this is a static table that stores areas I service tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes ...
0
votes
2answers
96 views

Trigger calls and application code

When a trigger is set on a table does the application code wait for the original statement and the trigger to execute before resuming or just the original statement? Consider application code to be ...
1
vote
1answer
6k views

mysql trigger to insert/update date column from datetime column

probably simple question but can't get it to work: I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them. ...
1
vote
1answer
2k views

MYSQL Trigger not working

I am trying to create the following trigger: CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta FOR EACH ROW BEGIN if new.wp_metakey = first_name then insert into ...
1
vote
1answer
3k views

filling a table by triggering multiple tables on insert

I have a table m contains the following fields: id int(11) not null auto_increment, //PK name varchar(100) null, something_else varchar(100) default 'not important' I need to fill up table m by ...
0
votes
2answers
980 views

Trigger needs dbms_alert declared

A trigger has given me the following: ORA-04098: trigger 'adminuser.DB_ERROR_CHANGE_TRIG' is invalid and failed re-validation and this is when working on altering schemas/triggers/etc from an ...
1
vote
2answers
191 views

Audit table insert restriction

So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ...
4
votes
1answer
2k views

MySQL possibility to create global routines (stored procedures and/or functions)

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database. When I tried to create a routine from console (without prior ...
0
votes
1answer
416 views

Trigger - Add to a date using a subquery

(postgresql 9.2) I'v 2 tables Table 1 (extinguisher) extinguisher_id (serial) type_designation_extinguisher_type(text) manufacturing_date (date) life_date(date) Table 2 (extinguisher_designation) ...
2
votes
1answer
501 views

pt-online-schema-change tool will work for existing triggers for a table?

I have a table name "user" ,I have tried to do partition the table and it was having a trigger already. When i have tried to alter the table with pt-online-schema-change.It was showing the below ...
0
votes
2answers
542 views

What am I doing wrong in mysql database?

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message: MYSQL ERROR: Can't update table 'player_viplist' in ...
4
votes
3answers
3k views

How to know the user doing a delete action on an audit table, when using a shared login?

Background information: I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app. Audit records are created via triggers. DML in my app's ...
3
votes
2answers
2k views

Table data transfer using SSIS and triggers on destination table

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
0
votes
2answers
97 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 = ...
2
votes
1answer
711 views

Counting the number of calls of a stored procedure

I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data). How can I let a counter ...
0
votes
2answers
1k 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
5k 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
1k 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
461 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 ...
5
votes
2answers
3k 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 ...
3
votes
1answer
20k 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
983 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
432 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
134 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 ...
1
vote
1answer
6k 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
594 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
448 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 ...
6
votes
1answer
2k 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
6k 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
4k 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
988 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
4answers
4k 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 ...
-4
votes
3answers
106 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
996 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 ...