Tag Info

New answers tagged

2

Here is the sample for DDL trigger that captures SQL query too: CREATE TRIGGER Audit_DDL ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS DECLARE @event xml; SET @event = EVENTDATA(); INSERT INTO Audit_DDL_Events VALUES ( REPLACE(CONVERT(varchar(50), @event.query('data(/EVENT_INSTANCE/PostTime)')), ...


1

This behaviour is a security feature to make hacking more difficult, specifically because there is no variation in the output depending on the input. (Aside from exposing the fact there is a logon trigger active.) If a password is mistyped, the system doesn't respond with an error saying "the password is wrong" or even whether the login name specified ...


8

You can create a view to hide the rest of the rows when they do exist: CREATE VIEW dbo.Top1000Rows AS SELECT TOP (1000) ...columns... FROM dbo.table_name ORDER BY DateCreatedUTC DESC; GO Then periodically run this in a background job (just play with the frequency until you're happy with how often the table itself is "off", and by how much): ...


2

Single trigger function for multiple triggers: possible - that's what you do. Single trigger for multiple tables: not possible. But you can shorten the code for creating lots of triggers: DO $$ BEGIN EXECUTE ( SELECT string_agg('CREATE TRIGGER touch_users BEFORE UPDATE ON ' || quote_ident(t) || ' FOR EACH ROW WHEN (OLD.modification_time IS NOT ...


1

Your trigger function can be improved: CREATE OR REPLACE FUNCTION trg_insup_bef_recipe() RETURNS TRIGGER AS $func$ BEGIN IF TG_TABLE_NAME = 'recipe_' THEN IF TG_OP IN ('INSERT', 'UPDATE') THEN NEW.is_baked_ := NEW.baking_instructions_ <> ''; NEW.is_roasted_ := NEW.roasting_instructions_ <> ''; END IF; RETURN NEW; ELSE ...


1

You could implement all your inserts on those tables via stored procedures. That would allow you to execute your check_percentage process at the end of said stored procedure. It would also allow you to impose additional conditions on said execution.


2

I know this post is fairly old and sorry for picking it up but I recently had the same requirement, so I created a MySQL UDF that uses JNI to call Java code from within a MySQL trigger. I'm posting this as a reference for similar future requests. I've checked in the code in bitbucket. You can download it from here: MySQLUDFJavaLauncher and here is also a ...


4

When you issue an ALTER TRIGGER statement, that process will attempt to acquire a SCH-M (schema modification) lock on the table object as well as the trigger object. This is an extremely low-concurrency lock and will cause the expected blocking that you're running into. See this reference for a chart on lock compatibility. For referencing sake, here is ...



Top 50 recent answers are included