Создаем таблицу, где будем помечать что нужно удалить ВСЕ датчики
Код
USE [bn_patp_dob]
GO
/****** Object: Table [dbo].[NoAllSens_devices] Script Date: 05.02.2014 17:14:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NoAllSens_devices](
[id] [int]IDENTITY(1,1) NOT NULL,
[device_code] [varchar](16) NOT NULL,
CONSTRAINT [PK_NoAllSens_devices] PRIMARY KEY CLUSTERED
(
[id]ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]) ON
[PRIMARY]GO
SET ANSI_PADDING OFF
GO
Теперь меняем тригер на нужный
Код
USE [bn_patp_dob]
GO
/****** Object: Trigger [dbo].[No19Sens] Script Date: 05.02.2014 17:09:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[No19Sens]
ON [dbo].[SYS_DEV_DirtyData]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
update DD
set dd.[digit_sens_num] = case when i.digit_sens_num&power(2,19-1)>0 then i.digit_sens_num^power(2,19-1) else i.digit_sens_num end
FROM inserted as i
inner join [dbo].[SYS_DEV_DirtyData] as dd on dd.id=i.id
inner join [dbo].[No19Sens_devices] as No19 on No19.device_code=i.device_code
-- Insert statements for trigger here
update DD
set dd.[digit_sens_num] = 0
FROM inserted as i
inner join [dbo].[SYS_DEV_DirtyData] as dd on dd.id=i.id
inner join [dbo].[NoAllSens_devices] as NoAll on NoAll.device_code=i.device_code
END
таблица NoAllSens_devices - хранит данные по номера приборов, по которым надо будет очищать вообще все датчики