Tag Info

New answers tagged

4

The problem is that the trigger was initially written with very simplistic logic and with minimal testing - it assumed that the update or insert could only ever affect a single row at a time. The line you are asking about is attempting to assign a single value to a variable from a table that may contain more than one row (triggers in SQL Server fire per ...


0

I am generally opposed to triggers. They have their place but surprising behaviour, when you do one simple thing and the change ripples out across the world, will give you headaches later. Instead of having storage_size and memory_usage on the users table, how about creating a view that displays the data you are looking for? Then you don't need triggers ...


1

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct ...


0

Yes, you are correct. You can often get bitten from the fact that the triggers are missing on the slave and thus the trigger effects are not there. Also if the triggers use any routines (PROCEDUREs and FUNCTIONs) you have to make sure they are present on the slave too. The latter might bite you if you provision your slaves from a mysqldump, since by default ...


-1

Nice work thanks to Nigel Rivett for sharing his work... perfect http://www.nigelrivett.net/AuditTrailTrigger.html /* This trigger audit trails all changes made to a table. It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed. It will put out an error message if there is no primary key on the table You ...


0

I would use dynamic sql: DECLARE @fldname varchar(max) = 'foo' Declare @sql NVARCHAR(MAX) = ' UPDATE TABLE SET ' + @FldName + ' = bar WHERE ' + @FldName + ' <> bar ' EXEC(@sql) In this example, this sets @sql to: UPDATE TABLE SET foo = bar WHERE foo <> bar And executes the script. Clearly, you would want to modify for your exact ...


0

Track the activity on your server with one of the off the shelf audit plugins available. McAfee Audit would be my recommendation here sincemyou likely just want to know calls filtered around the stored procs.


0

You could capture the mysqld's uptime or the DB Server's time at the start and end of your code. Perhaps something like this SUGGESTION #1 SELECT variable_value INTO @t1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE variable_name='uptime'; <Your Stored Procedure Code> SELECT variable_value INTO @t2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE ...


1

The TRIGGER can be simplified down to INSERT ... ON DUPLICATE KEY UPDATE ... It is not proper to have "redundant" data in a database. Why can't you just leave it in the original table? When you need it, do a JOIN.



Top 50 recent answers are included