Tag Info

New answers tagged

0

You cannot change the rest of rows on table with its own trigger- that is by design. Check this answer. That also provides an alternative method: disable the INSERT privileges on that table and allow inserting only by using a stored procedure, that will do as you want (although I do not like much the logic behind it): CREATE PROCEDURE ...


3

Regular trigger will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.


1

I got some interesting news for you. You cannot use information_schema.tables because it does not track changes to InnoDB tables. The best way to find out when a table changed is to go to the OS and get the most recent timestamp of each table. Rather than plagiarize my own posts, here are my posts where I show you how to do this Sep 25, 2014 : want to ...


0

It sounds like you're asking whether it's possible to alter a trigger in an atomic operation, where, if the new definition fails, you don't lose the old one... similar to CREATE OR REPLACE VIEW, which replaces the view definition if the new definition is valid, but leaves the old one in place, if you can't replace it. Unfortunately, there's no ALTER ...


1

What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?) You are going to have to drop the triggers and recreate them I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?) Here is ...


1

The behaviour you've described is correct and documented : http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS If the trigger have been created with double quote and special char, it must be used with the same syntax.


0

Remove the "" - so it will be case insensitive. Please also remove the "" when creating the trigger.


8

Since it's only UPDATE,INSERT you can say: IF EXISTS (SELECT 1 FROM deleted) -- update ELSE -- insert You have a bigger problem, though. There is no such thing as new.Id, and an insert or update can affect multiple rows (in some platforms, triggers fire per row; in SQL Server, they fire per operation). So you need to either: Use a loop to call the ...


1

Posting this as an answer to address the core question: Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not ...


1

You could just use a FOR trigger instead of an INSTEAD OF trigger. The records, as they were before the DML statement, are still in the deleted table (for updates and deletes). Everything will still be contained in an implicit transaction. In my opinion, INSTEAD OF triggers are really handy when you don't want to run the original DML, or on views.


1

You are defining an ordinary AFTER trigger. So use an AFTER trigger, not an INSTEAD OF trigger. The cases where you want to use 'instead of' triggers are, basically, none.



Top 50 recent answers are included