How to write if condition in Trigger

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

below is my code..

SQL

USE [PRSAppTest_TST]
GO
/****** Object:  Trigger [dbo].[trg_URPatients]    Script Date: 04/26/2013 03:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_URPatients] 
  ON  [dbo].[Patients]
  FOR INSERT
AS 

BEGIN
 
DECLARE @PCPID varchar(50)
DECLARE @Cnt int
DECLARE @PatientID varchar(50)
 
SET @PatientID =  (SELECT top(1) PatientID from INSERTED)
SET @PCPID = (select top(1) UserId from users where PCPGroupID=(SELECT top(1) PCPID from INSERTED))
if @PatientID =NULL
begin
return
end
else
begin
insert into UserRef(PatientID,AssignedBy,AssignedTo,ReasonOfAccs,EndDate) 
values(@PatientID,@PCPID,@PCPID,'PCP',Dateadd(d,60,GETDATE()))
end
 
END

Here above is my code...is that write.

Posted 26-Apr-13 1:30am

ntitish

Updated 26-Apr-13 1:32am

ZurdoDev

v2

Add a Solution

Comments

ZurdoDev 26-Apr-13 7:33am

What's your question?

ntitish 26-Apr-13 7:39am

@PatientID = (SELECT top(1) PatientID from INSERTED)

if i got above @patientID=null means it should return else it should go into else part. but my query is showing wrong

ntitish 26-Apr-13 7:46am

sir i had one more dought...can we alter the trigger code

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-04-26T02:09:00

Hi,

You are Compaing NULL in wrong way. Use "IS NULL" for NULL Comparision not "=".
Check the links...
Comparison Operators (Transact-SQL)[^]
SQL Operators[^]

-- I changed your Trigger Code. Check the following Script....

SQL

USE [PRSAppTest_TST]
GO
/****** Object:  Trigger [dbo].[trg_URPatients]    Script Date: 04/26/2013 03:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_URPatients] 
  ON  [dbo].[Patients]
  FOR INSERT
AS 
 
BEGIN
 
DECLARE @PCPID varchar(50)
DECLARE @Cnt int
DECLARE @PatientID varchar(50)
 
SET @PatientID =  (SELECT top(1) PatientID from INSERTED)
SET @PCPID = (select top(1) UserId from users where PCPGroupID=(SELECT top(1) PCPID from INSERTED))
if @PatientID IS NULL
begin
return
end
else
begin
insert into UserRef(PatientID,AssignedBy,AssignedTo,ReasonOfAccs,EndDate) 
values(@PatientID,@PCPID,@PCPID,'PCP',Dateadd(d,60,GETDATE()))
end
 
END

GVPrabu

Sant Osha · Accepted Answer · 2013-04-26T02:29:00

i changed you code:

SQL

USE [PRSAppTest_TST]
GO
/****** Object:  Trigger [dbo].[trg_URPatients]    Script Date: 04/26/2013 03:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_URPatients] 
  ON  [dbo].[Patients]
  FOR INSERT
AS 
 
BEGIN
 
DECLARE @PCPID varchar(50)
DECLARE @Cnt int
DECLARE @PatientID varchar(50)
 
Select @PatientID =  top(1) PatientID from INSERTED
SELECT @PCPID = top(1) UserId from users where PCPGroupID=(SELECT top(1) PCPID from INSERTED)
if @PatientID is NULL
begin
return 
end
else
begin
insert into UserRef(PatientID,AssignedBy,AssignedTo,ReasonOfAccs,EndDate) 
values(@PatientID,@PCPID,@PCPID,'PCP',Dateadd(d,60,GETDATE()))
end
 
END

How to write if condition in Trigger

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to write if condition in Trigger

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us