Procedural code automatically executed in response to a database event.
5
votes
2answers
282 views
considerations when using triggers on a replicated (target) database
Our SQL Sever 2008 application database is replicated from Server A to Server B (push replication). We use the copy, let's call it database_b, on Server B to create reports and run other queries so ...
2
votes
1answer
574 views
Msg 7391, Distributed Transactions (DTC) on SQL Server
This may be DBA related or developer related, I honestly don't know.
I have two Database servers:
A: SQL Server 2005 (Trigger that uses DBMail on Server B)
B: SQL Server 2008 R2 (DBMail configured)
...
1
vote
0answers
203 views
Trouble with triggers and insert .. on duplicate key update
I have two tables with identical schemas. one is a "main" table that is pruned periodically and kept small. The other is a "reporting" table that is an archive of everything that ever was. For ...
2
votes
2answers
299 views
Comparing dates in a BEFORE INSERT trigger
I need help making a TRIGGER that compares two dates being entered into a table, but I'm not 100% on the syntax I should use.
Something like:
CREATE TRIGGER chk_dates
BEFORE INSERT ON ...
1
vote
1answer
196 views
Inherit audit columns and triggers
Background
I have a database with dozens of tables, some of which should have audit columns.
Problem
I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
2
votes
1answer
201 views
trigger in MySQL to disallow multiple row deletions at a time on a table
How do we create a trigger in MySql to disallow deletion of more than 2 rows from (say) employee table by a single query?
delimiter \\
create trigger not_more_than_2_rows
before delete on employee
...
1
vote
1answer
181 views
How to write signal function in MySQL that can be called from Triggers and Stored Functions?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
The Table Description:
mysql> DESC Employee;
...
2
votes
1answer
442 views
What is the difference between ON SCHEMA and ON DATABASE triggers?
We can create the database trigger on concrete schema event (ON SCOTT.SCHEMA) or on all schemas (ON SCHEMA). However, we can also use ON DATABASE when creating database trigger. What is the difference ...
1
vote
1answer
159 views
Is it possible to monitor role and privilege grant/revoke using triggers?
Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.
3
votes
1answer
429 views
Why I don't need to COMMIT in database trigger?
We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database ...
7
votes
1answer
624 views
Why am I NOT getting a mutating table error in trigger?
It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:
A mutating table is a table that is being modified ...
0
votes
1answer
113 views
How triggers are called when we have two triggers?
In a table I have two triggers. When it is called for the first time the first trigger does not fires while the second trigger's got fired. On the next call both first and second trigger action take ...
1
vote
2answers
49 views
PostgreSQL custom domain with trigger
Is it possible to create custom domain / data type and associate some trigger with it, for example hash password?
I will have for example password domain and if I set some value to this column, it ...
1
vote
1answer
137 views
processlist showing '%' as host from trigger
I'm trying to populate a certain column in a table with the host/IP of the caller. I have an insert trigger on the table which sets the column like so:
CREATE TRIGGER access_insert_trg BEFORE INSERT ...
3
votes
1answer
116 views
Restrict record to be update or deleted according to dates inside row for SQL Server 2005
I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher.
I now know that if someone updates, ...
3
votes
2answers
442 views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: ...
1
vote
0answers
88 views
TSQL Trigger Incorrect Syntax
Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G:
INSERT INTO aTable
(
tTypeId,
opId,
inserted.Id
)
SELECT
3,
0,
inserted.Id
...
2
votes
2answers
389 views
Postgresql after 'for each row' triggers don't actually execute after each row?
If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted ...
1
vote
1answer
220 views
Performance of SQL Server Triggers
I'm coming from an Oracle/PostgreSQL background and have a hard time coping with the limits of SQL Server's trigger implementation.
I want to update two different datetime columns if the value of a ...
3
votes
1answer
141 views
Error when UPDATE all rows
When I update a single row, it works fine. But when I update all rows with:
UPDATE cad_bilhetes
SET ligacao_acobrar = 'False'
I get the following error:
Msg 512, Level 16, State 1, Procedure ...
2
votes
3answers
150 views
How to trigger an action without using triggers
I just want to ask. We have a calendar table, let's call it CAL1 in one database, lets call it DB1. CAL1 is manually populated. Now in another database, DB2, there is another calendar table CAL2. What ...
3
votes
1answer
126 views
Check what event called the trigger on PostgreSQL?
I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE.
Oracle triggers can check this in a simple 'IF' statement:
IF INSERTING, IF UPDATING or even IF DELETING
Is there a ...
4
votes
3answers
939 views
Restrict update on certain columns. Only allow stored procedure to update those columns
I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
1
vote
1answer
357 views
How do triggers affect last_insert_id()?
I am unable to test this right now, however I'm curious about the answer.
If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B? ...
3
votes
1answer
129 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]
(
...
1
vote
1answer
2k 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
619 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 ...
4
votes
1answer
136 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 ...
1
vote
1answer
49 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 ...
3
votes
1answer
5k 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
53 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 ...
12
votes
3answers
813 views
Slow deletion of records when a trigger is enabled
Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve.
http://support.microsoft.com/kb/2606883
Ok so I have an ...
2
votes
2answers
185 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 ...
2
votes
1answer
609 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
0answers
82 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
2answers
190 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 = ...
2
votes
2answers
1k 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
764 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
275 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 ...
-3
votes
1answer
1k 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
1answer
518 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 ...
4
votes
2answers
375 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 ...
5
votes
1answer
2k 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
247 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 ...
1
vote
1answer
213 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]
...
2
votes
1answer
333 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
378 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
642 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 ...
0
votes
1answer
341 views
How to update another table using triggers in postgresql [closed]
I'm using PostgreSQL.
I've 2 tables in my schema.
One called taggings where I've (id, film_id, tag_id, user_id), all integers.
And another where I intend to sum tags, called film_tag_counts (id, ...
1
vote
1answer
336 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 ...
