Tag Info

New answers tagged

8

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...


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



Top 50 recent answers are included