i have created a view from two tables in my database (ProjectedOutputs, and Output)
since I am using the view in vb to fill a grid, i am trying to set up a INSTEAD OF DELETE trigger to delete any records in ProjectedOutputs that may be deleted from my grid. Although the records are deleting OK, so are the records in Output, which i dont want. i am not sure how to stop this, or to see if my trigger is actually firing, I would appreiciate any suggestions
CREATE TRIGGER Test
INSTEAD OF DELETE
FROM PROJECTEDOUTPUTS JOIN deleted ON PROJECTEDOUTPUTS.ProjectedOutputID = deleted.ProjectedOutputID
The test trigger you posted will delete from PROJECTEDOUTPUTS, and only PROJECTEDOUTPUTS.
Are you sure the data is also being deleted from OUTPUTS? If so, then it is something that your application is doing, or there is possibly a trigger or relationship on the PROJECTEDOUTPUTS table itself which is cascading deletes to OUTPUTS.
If it's not practically useful, then it's practically useless.
ah ha, i have found something on microsoft help and support(
PRB: Trigger Does Not Fire on View When Updated Through ADO ) that says that triggers wont fire unless i use view metadata attirube for a view. It describes the poblem completely, but i cant work out the fix, when i used view metadata, then i get an error msg saying that i cant update my recordset because modification affects multiple base tables
I feel like i am taking one step forward and three back, any ideas?
CREATE VIEW V_ProjectOutputs
WHERE P.OUTPUTID = O.OUTPUTID
and now my trigger is firing, many thanks to those who helped me