Procedural code automatically executed in response to a database event.

learn more… | top users | synonyms (1)

3
votes
1answer
240 views

For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns? Like if I have this table: CREATE TABLE [dbo].[tbl_shipping_boxes] ( ...
2
votes
2answers
10k views

Declaring and using variables in triggers

I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem. DELIMITER $$ USE `pucko`$$ CREATE TRIGGER `after_delete_Alien` AFTER ...
2
votes
2answers
3k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
1
vote
1answer
71 views

Deadline rule date input after now

Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date. Currently I'm attempting this with a rule. CREATE OR REPLACE RULE "DeadlineInput" AS ON INSERT ...
5
votes
1answer
253 views

Prevent users deleting more than a certain number of rows in MySQL

The problem: I have a table with rows, nothing special about it When a database user removes a row, it should increment a number A database user is only allowed to remove 3 rows max in the table A ...
4
votes
1answer
23k views

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

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
0
votes
0answers
74 views

memc_delete called from a MySQL trigger loop fails randomly

I'm calling memc_delete from a MySQL trigger's code. The call is inside a loop. Logging the results, I see that memc_delete sometimes returns 1 (ok, memcached entry deleted) and sometimes returns 0 ...
1
vote
1answer
5k views

MySQL trigger - delete data from multiple tables

I would like to delete data in multiple tables, from a trigger but it seem like my syntax is incorrect. I got the error: "you've got a error in your MySQL syntax please check the manual". This ...
2
votes
0answers
103 views

SQL Server and updating windows firewall rules

Any ideas how should I proceed if I would like to update the firewall rules (essentially add ip-s to existing firewall rule). The step of exporting ip-s to text file and then using command line to ...
2
votes
1answer
2k views

SQL Server trigger (update or insert with inner join problem)

ALTER TRIGGER [dbo].[killertrigg] ON [dbo].[tbl_pvporderview] AFTER UPDATE AS IF (UPDATE([Kill])) BEGIN SET NOCOUNT ON; INSERT killer(serial,[Kill]) SELECT i.serial,i.[Kill] FROM ...
2
votes
2answers
609 views

Error updating sql server geography type in trigger

I have the following trigger in my database: CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS IF UPDATE(Latitude) UPDATE pt SET pt.GeocodedAddress = ...
3
votes
2answers
5k views

MySQL: Loop over cursor results ends ahead of schedule

I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that: EDIT: Sorry, stupid mistake. The trigger is executed ...
2
votes
1answer
2k views

SQL Select WHERE row updated

USE [RF_WORLD] GO /****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ...
2
votes
1answer
566 views

create trigger which inserts rows depending on enum values?

I'm quite new to triggers and I ran into general problems. I'd like to achieve this: CREATE TABLE `searcharticles` ( `articleID` int(11) unsigned NOT NULL, `ean` char(13) COLLATE utf8_unicode_ci ...
2
votes
2answers
420 views

simulate sequence on mysql in preparation for move to oracle

I've been developing an application against MySQL while a new development server has been slowly being set up. The new dev server will use oracle, and since we're getting close to it being ready, I ...
9
votes
1answer
4k views

Should I add SET NOCOUNT ON to all my triggers?

It is fairly common knowledge that you should have SET NOCOUNT ON by default when creating new stored procedures. Microsoft has changed the default template to include this in 2012. I thought ...
7
votes
1answer
10k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...
2
votes
1answer
518 views

Skip running trigger during cascade delete?

I have a legacy application where I have two tables X and Y. Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X ...
-3
votes
1answer
2k views

How to Create a Trigger in SQL Server to Update Cascade When @@RowCount > 1?

I Have 2 Table Table1 | column name | type | |-------------+-------------| | ID1 | Int | | Name | NVARCHAR(50)| Table2 | column name | type | ...
5
votes
2answers
892 views

Implementing a Circular Buffer (Sliding window) in MySQL

I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the ...
2
votes
1answer
1k views

Looking for a Trigger to change inserting strings?

I have an MSSQL Server 2008 Database. There are some tables in my Database like below : table1, table2, table3, .... each table has a NVARCHAR(50) field (Name) Now, I wanna change the strings that ...
1
vote
1answer
688 views

How can I trigger this stored procedure on insert?

I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous ...
1
vote
1answer
3k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
1
vote
1answer
808 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 ...
1
vote
1answer
2k views

Keeping 2 tables in sync

I need to write a trigger that will keep one table synced with 2 other tables. I have table_a, table_b and table_c on slave. The master has only table_a and whenever this table is updated on master, ...
2
votes
2answers
331 views

Is it possable to do a ALL SERVER DML Trigger?

My department works on converting a client's database when a client switches from another company to us. We have a RDP session they can connect to and see our software with their data. Once they ...
1
vote
1answer
528 views

MySQL: Try to Port a Trigger from SQL Server

Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause: CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg] -- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg] ON [dbo].[Test_PTA_Table] ...
1
vote
3answers
192 views

A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete

THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line. THe trigger thus far is: CREATE OR ...
1
vote
1answer
647 views

when will Innodb support activation of triggers on cascade foreign key? [closed]

The Manual says: http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html Currently, cascaded foreign key actions do not activate triggers. Any Idea when will this be implemented. Is ...
1
vote
1answer
1k views

bad bind variable in trigger

i'm trying to update the table whenever complaint has been registered create or replace trigger complaint_advertise BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW DECLARE BEGIN UPDATE ...
2
votes
1answer
669 views

How to speed up audit trigger?

The task: write a trigger to log all updates within tbl1 table for future audit users' actions. The tbl1 table: id INT(11) y SMALLINT(6) country SMALLINT(6) -- really here ~20 fields but only some ...
2
votes
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
1
vote
2answers
2k views

Adding Row Version to SQL Server table

I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
1
vote
1answer
481 views

Error with Oracle trigger, invalid identifier

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working. DML statement UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT ...
5
votes
2answers
774 views

Transaction independent writes inside a trigger

I am using sql express 2008 r2 express edition. Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire? I have triggers ...
5
votes
1answer
226 views

Triggers and the Transaction

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic. The logic is ...
3
votes
1answer
976 views

How to qualify variable inside trigger body (PL/SQL )?

I can explicitly qualify variable in the body of stored procedure or function : create or replace PROCEDURE ptest AS int_val INT; BEGIN ptest.int_val:=0; END; / How to do the same inside trigger ...
1
vote
1answer
626 views

Triggers not being run on a partitioned table

I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
1
vote
1answer
1k views

Soft delete: Copy row data to archive table when deleting

How can I move data from my current table to an archive table when a delete is called on the table? My current table and archive table needs to be in sync in their schema too. Can someone help ...
2
votes
1answer
932 views

MySQL using trigger instead of check

Is there any way that I could use trigger instead of check? like I have CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER test BEFORE UPDATE ON apply FOR EACH ROW BEGIN ...
4
votes
2answers
222 views

Can I be notified of a rollback in my DDL Trigger?

I'd like DDL changes in SQL Server to trigger an external process (outside of SQL). The problem is, is there a way for me to be notified via my trigger that a rollback occurred? For instance, with ...
2
votes
2answers
169 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
1
vote
1answer
846 views

SQL Server sp_send_dbmail Query Error

EXEC msdb.dbo.sp_send_dbmail @recipients= 'test@test.com', @copy_recipients = 'test@test.com', @query = 'SELECT * FROM INSERTED', @attach_query_result_as_file = 1, @subject = 'Auto-Generated Deleted ...
1
vote
1answer
210 views

Randomly generated password for mysql admin over time?

I have this idea of making my database's Admin account secured by creating the following: Trigger is activated every hour in mysql this trigger activates a stored procedure Stored procedure ...
2
votes
2answers
1k views

How to specify trigger execution order under PostgreSQL?

I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table. CREATE TABLE twitter_interactions(...); CREATE OR REPLACE ...
0
votes
1answer
96 views

Checking for ApplicationName in a transaction through a Before Trigger

I would like to be able to build a trigger that checks the ApplicationName coming across on a transaction, and if the ApplicationName is of a certain value, then I want to throw an error, saying the ...
4
votes
2answers
6k views

In a trigger, how to check if no fields have changed?

If we receive an update statement that does not check if the value has changed in the where clause, what are the different ways to ignore that update inside a trigger? I know we can do a comparison ...
4
votes
3answers
4k views

Disable trigger for just one table

Is it possible to disable a trigger momentarily but for just one table. For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
6
votes
4answers
23k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
2
votes
1answer
340 views

MySQL Trigger problem with the email address (cause contains the @ sign)

I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger: CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW ...