New answers tagged

0

With Scott Hodgin's help (see comment thread above), I was able to figure out the issue: First issue: Since I certainly was using an OLE DB destination, I needed to add FIRE_TRIGGERS in the Advanced Editor (Microsoft, make this easier to find) Second issue: once the INSERT trigger was firing, it was updating every record in the table, not just the ones ...


0

And I want to insert the entire row value in another table. Be more precise -- which "row value" are you referring to? Assuming you want to copy the before-update values from profile to profile_history, then: from trn_student_profile where trn_student_profile.StudentID != NEW.StudentID AND trn_student_profile.SchoolID != NEW.SchoolID; --> from OLD ...


1

Use a CASE to evaluate it value: CREATE TRIGGER [dbo].[Logging] ON [dbo].[TriggerTest] For UPDATE AS BEGIN IF UPDATE (ColumnName) Begin INSERT INTO [dbo].Logging( CorrelationID, DateTimeStamp, Operation ) SELECT i.ID, GETDATE(), CASE WHEN i.ColumnName is not null and d....


0

I cannot figure out what exactly is the problem, but I suggest that you add something like this after the update of table_a: IF NOT FOUND THEN RAISE EXCEPTION 'row % in table_a not found!', NEW.recida; END IF; Then at least you can keep the inconsistency from happening. If the trigger fails, all the effects of the statement and the trigger will be ...


1

You add a (redundant) unique constraint on (study_id, id) on both the treatment and visit tables. Then let the foreign keys from session refer to these unique constraints by including study_id into the respective foreign keys. Then your condition will automatically be satisfied. The unique constraints are not quite as superfluous as they seem, because the ...


0

Schematically (no syntax): CREATE TRIGGER UpdateThingsNumberTrigger ON Things AFTER INSERT AS WITH cte AS ( SELECT LEFT(Number, 8) yyyymm, CAST(RIGHT(MAX(Number),4) AS INT) nnnn FROM Things GROUP BY LEFT(Number, 8) ) UPDATE Things LEFT JOIN cte ON cte.yyyymm = YEAR(CreationDate) + '.' ...


1

You're almost there. Your trigger should probably look like this: CREATE TRIGGER update_sire_name before UPDATE ON table_name for each row set new.sireName = (select name from table_name where regNo = new.sireRegNo) dbfiddle 1 Yes, there can be a where clause in a trigger. 2 Consider using numeric data types (e.g. INTEGER) where values are ...


0

Make an after-update trigger on "customer" which does a dummy update on "project", which will then fire the trigger on project to do its thing: CREATE OR REPLACE FUNCTION customer_depend_trigger() RETURNS trigger AS $$ BEGIN update project set customer_id=NEW.id where customer_id=NEW.id; RETURN NEW; END; $$ LANGUAGE plpgsql; create ...


4

This isn't really an "apples to apples" comparison. The trigger version of the query looks like this: UPDATE dbo.Subscriber SET IdSubscribersStatus = 1 FROM @Subscribers s2 JOIN dbo.Subscriber s ON s2.IdSubscriber = s.IdSubscriber OPTION(force order, fast 1) And the manual version of the query looks like this: update dbo.Subscriber set ...


Top 50 recent answers are included