I am setting variables to a select statement. It is working, but I hear this is a bad practice. Can someone tell me if what the trigger is doing is a good practice.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [Dpc_Add_Zone_OC_History] ON [dbo].[zone]
FOR INSERT, UPDATE
AS
Declare @icnt int
,@dcnt int
,@tran_type char(1)
,@ZoneID Char(6)
,@Branchno int
,@SchedNo int
,@ArmDisarm char(1)
,@SystemNo int
Declare @Found int
select @icnt = count(*) from inserted
select @dcnt = count(*) from deleted
if @icnt = @dcnt select @tran_type = 'C'
if @icnt > @dcnt select @tran_type = 'A'
if @icnt < @dcnt select @tran_type = 'D'
if @tran_type in ('A','C')
Begin
Set @ZoneID = (Select Zone_id from inserted)
Set @BranchNo = (select top 1 branch_no
from site s with (nolock)
inner join system sys with (nolock) on sys.site_no = s.Site_no
inner join inserted i on i.system_no = sys.system_no)
Set @SchedNo = (Select top 1 IsNull(Sched_no,1)
from processing_rule pr
inner join system sys with (nolock) on sys.system_no = pr.System_no
inner join inserted i on i.system_no = pr.system_no
where pr.zone_id = i.zone_id)
if @ZoneID like 'O/C%'-- and @BranchNo = 20
Begin
Set @ArmDisarm = (Select arm_disarm from inserted)
If @ArmDisarm = 'A'
Begin
Update Zone_OC_History_DPC
Set Close_Date = i.status_change_date
From Zone_OC_History_DPC ZOCH
inner join inserted i on Zoch.system_no= i.system_no
where ZOCH.Arm_Disarm = 'D'
and zoch.sched_no = @SchedNo
and Close_Date is Null
Insert into Zone_OC_History_DPC
Select System_no,Zone_id,@SchedNo,Arm_Disarm,Status_Chang e_date,DatePart (dw,Status_Change_date),Null,Null
From inserted i
End
Else
if @ArmDisarm = 'D'
Begin
Set @Found = (Select count(zoch.System_no)
from Zone_OC_History_DPC ZOCH
inner join inserted i on i.system_no = ZOCH.system_no
Where ZOCH.system_no = i.system_no
and ZOCH.sched_no = @SchedNo
and Zoch.Arm_Disarm = 'D'
and ZOCH.Close_date is Null
and zoch.status_change_date = i.status_change_date)
If @Found = 0
Begin
Insert into Zone_OC_History_DPC
Select System_no,Zone_id,@SchedNo,Arm_Disarm,Status_Chang e_date,DatePart(dw,Status_Change_date),Null,Null
From inserted i
End
End
End
End