Tag Info

New answers tagged

1

You could start with ORIGINAL_LOGIN(), SUSER_SNAME(), HOST_NAME() and 'APP_NAME()'. However as @JeroenB said you may have better luck creating a trace or extended events session. You can get the above information and more that way. Make sure to restrict down the trace/EE session to just the one table and you shouldn't see a lot of overhead. You could ...


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 ...



Top 50 recent answers are included