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 ...
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:
where trn_student_profile.StudentID != NEW.StudentID
AND trn_student_profile.SchoolID != NEW.SchoolID;
Use a CASE to evaluate it value:
CREATE TRIGGER [dbo].[Logging]
IF UPDATE (ColumnName)
INSERT INTO [dbo].Logging(
WHEN i.ColumnName is not null and d....
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;
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 ...
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 ...
Schematically (no syntax):
CREATE TRIGGER UpdateThingsNumberTrigger
WITH cte AS ( SELECT LEFT(Number, 8) yyyymm, CAST(RIGHT(MAX(Number),4) AS INT) nnnn
GROUP BY LEFT(Number, 8)
LEFT JOIN cte ON cte.yyyymm = YEAR(CreationDate)
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)
1 Yes, there can be a where clause in a trigger.
2 Consider using numeric data types (e.g. INTEGER) where values are ...
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 $$
update project set customer_id=NEW.id where customer_id=NEW.id;
$$ LANGUAGE plpgsql;
This isn't really an "apples to apples" comparison. The trigger version of the query looks like this:
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: