How to use if updating on nested column in triggers
CREATE TRIGGER [dbo].[insupddel_your Table] ON [your Table] FOR INSERT, UPDATE, DELETE AS IF @@ROWCOUNT = 0 return SET NOCOUNT ON; DECLARE @action nvarchar(10), @ins Count int = (SELECT COUNT(*) FROM inserted), @del Count int = (SELECT COUNT(*) FROM deleted) SELECT @action = CASE WHEN @ins Count declare @columns_count int = ??
-- number of columns in the table, @columns_updated_count int = 0 -- this is kind of long way to get number of actually updated columns -- from columns_updated() mask, it's better to create helper table -- or at least function in the real system with cte_columns as ( select @columns_count as n union all select n - 1 from cte_columns where n 1 ), cte_bitmasks as ( select n, (n - 1) / 8 1 as byte_number, power(2, (n - 1) % 8) as bit_mask from cte_columns ) select @columns_updated_count = count(*) from cte_bitmasks as c where convert(varbinary(1), substring(@columns_updated_mask, c.byte_number, 1)) & c.bit_mask 0 -- actual check if exists (select * from inserted) if exists (select * from deleted) select @operation = 'U' else select @operation = 'I' else if exists (select * from deleted) select @operation = 'D' else if @columns_updated_count = @columns_count select @operation = 'I' else if @columns_updated_count to see if the column is updated, but it's not safe for tables with big number of columns.
WHEN EXISTS(SELECT * FROM INSERTED) THEN 'I' -- Set Action to Insert. Based upon that information, I then created a small Action routine which determines why the trigger has been activated.
WHEN EXISTS(SELECT * FROM DELETED) THEN 'D' -- Set Action to Deleted. This type of interface is sometimes needed when there is both a common configuration and a specific action to occur on an INSERT vs. In these cases, to create a separate trigger for the UPDATE and the INSERT would become maintenance problem. were both triggers updated properly for the necessary common data algorithm fix?
00000 - "cannot modify a column which maps to a non key-preserved table" *Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.
Also, this approach will still incorrectly classifies some updates as inserts (if every column in the table is affected by update), and probably it will classifies inserts where there only default values are inserted as deletes, but those are king of rare operations (at lease in my system they are).00000 - "cannot perform DML on this nested table view column" *Cause: DML cannot be performed on a nested table view column except through an INSTEAD OF trigger *Action: Create an INSTEAD OF trigger over the nested table view column and then perform the DML.CREATE OR REPLACE TRIGGER trg_comp_vehicles_view_insrt INSTEAD OF INSERT ON NESTED TABLE vehiclelist OF company_vehicles_view REFERENCING NEW AS Vehicle PARENT AS Company FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table.) To that end, I would like to give the following multi-trigger event code snippet for handling INSERT, UPDATE, DELETE in one trigger for an Microsoft SQL Server.CREATE TRIGGER [dbo].[INSUPDDEL_My Data Table] ON [dbo].[My Data Table] FOR INSERT, UPDATE, DELETE AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with caller queries SELECT statements.
Say you try to delete where an ID equals some value that does not exist in the table.