Tag Info

Hot answers tagged

5

John is right that the updated syntax error message you get: Msg 1034, Level 15, State 1, Procedure trigger Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration. This is absolutely because the trigger is specified as FOR UPDATE, UPDATE. This syntax was allowed in older compatibility levels but not in modern ones. ...


4

The actual syntax for the command shows that there is no such thing as FOR UPDATE, UPDATE - or rather that it just doesn't make any sense: CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ...


2

There are several things wrong with this trigger. First: your delete statement. You can't compare NULL using =. You need to use IS NULL: DELETE FROM test2 WHERE email IS NULL; Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for." So return ...


2

You can avoid the whole problem by using this pattern. Old tables had id MEDIUMINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(id) New table has tenant_id SMALINT UNSIGNED, id INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(tenant_id, id), INDEX(id) Notes: INDEX(id) is sufficient to handle AUTO_INCREMENT. The ids for a given tenant will have a lot of gaps and ...


2

Here's a very quick example that will create a user blat given an existing login blat in all new databases created (this won't work for attach/restore). You could extend this to adding them to roles, granting permissions, even building the list based off of some query from sys.server_principals in master, sys.database_principals in another database, or your ...


1

This currently isn't practical. Writes may not be done on a downstream replica, whether it replays WAL via streaming or archives. However, the logical decoding feature in PostgreSQL 9.4 sets the foundations for making this sort of thing possible. You could have a logical decoding plugin that reads the change-stream from the master server, then writes audit ...


1

"Is it possible to do so? The trigger will insert some rows into a unique table that exists only on the slave." yes, you can A lot of warehousing architectures are based on the slave only. The title of your question is wrong. Change replica DB with SLAVE ONLY


1

I really like your idea of inserting XML before and after images of the changed data. IMHO, it is not light weight enough for a trigger. I've changed the whole WHILE loop to the following (sorry for the different names) insert into audit (table_name, old_content, new_content) select @table_name, case when d.table_id is null then null else (select ...



Only top voted, non community-wiki answers of a minimum length are eligible