If condition in trigger not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If condition in trigger not working

Author

Topic

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-08-28 : 08:43:48

USE [Religare1]
GO
/****** Object: Trigger [dbo].[NEWACTIVEID] Script Date: 08/28/2008 13:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[NEWACTIVEID] ON [dbo].[Client]
FOR INSERT
AS
BEGIN

Declare @clientid int,
@id int,
@pw char(2),
@activeid char(9),
@luhn char(9),
@Index SMALLINT,
@Multiplier TINYINT,
@Sum INT,
@Plus TINYINT,
@chek char(1),
@firstname varchar(50),@lastname varchar(50),
@persid int,@pword varchar(100),
@random varchar(5),
@newrelativeid varchar(20),
@oldrelativeid varchar(20),
@relativeid varchar(20),
@activeid1 varchar(20),
@relativeactiveid varchar (20),
@password varchar(40),
@name varchar(100),
@email varchar(100),
@rmcode int,
@phone varchar(20),
@createddate datetime,
@updateddate datetime
Select @random=cast(cast(rand()*100000 as int) as varchar(6))

Select @clientid=client_clientid,
@persid=client_personid,
@ID=CLIENT_ID,
@activeid=client_activeid,
@password=client_password,
@name=client_name,
@email=client_email,
@rmcode=client_rmname,
@phone=client_phonenumber,
@createddate=client_createddate,
@updateddate=client_updateddate,
@newrelativeid=client_relativeactiveid
From inserted

Select @oldrelativeid=client_relativeactiveid from deleted

If (@activeid is null)
Begin
Print 'Created Date is same as Updated date'
Select @firstname=pers_firstname, @lastname=pers_lastname
From person Where pers_personid=@persid
Set @lastname=replace(substring(@lastname,1,1),' ','')
Set @firstname=replace(substring(@firstname,1,6),' ','')
Set @pword=@firstname+@lastname+@random
Set @luhn=@id
SET @luhn=Replicate('0',6-Len(@Luhn))+@luhn

SELECT @Index = LEN(@Luhn),
@Multiplier = 2,
@Sum = 0
Print 'Index='+cast(@Index as char(1))
Print 'Multiplier='+cast(@Multiplier as char(1))
Print '@Sum='+cast(@sum as char(2))
Print '@Luhn='+@luhn

WHILE @Index >= 1
Begin
SELECT @Plus = @Multiplier * CAST(SUBSTRING(@Luhn, @Index, 1) AS TINYINT),
@Multiplier = 3 - @Multiplier,
@Sum = @Sum + @Plus / 10 + @Plus % 10,
@Index = @Index - 1

Print 'Index='+cast(@Index as char(1))
Print 'Multiplier='+cast(@Multiplier as char(1))
Print '@Sum='+cast(@sum as char(2))
Print '@Luhn='+@luhn
Select @chek=CASE WHEN @Sum % 10 = 0 THEN '0' ELSE CAST(10 - @Sum % 10 AS CHAR) END
Print @chek
Set @luhn='PW'+cast(@Luhn as char(6)) +@chek
Print @luhn
End
update client
set client_activeid=@luhn,client_password=@pword where client_clientid=@clientid
Insert Into
[PortfolioTracker].[DBO].JVID_Master(ModifiedDateTime,CreatedDateTime,JVIDMobileNo,JVID,password,JVIDName,JVIDEmail,Rmcode,ModifiedBy,CreatedBy)
Select getdate(),getdate(),client_phonenumber,client_activeid,client_password,client_name,client_email,client_rmname,'system','system'
From client Where client_clientid=@clientid
End
Else
Begin
Print 'created date is not same as updateddate'
Select @activeid=client_activeid From client Where client_clientid=@clientid
Update [PortfolioTracker].[DBO].JVID_Master
Set password=@password,jvidname=@name,jvidemail=@email,rmcode=@rmcode,jvidmobileno=@phone,ModifiedDateTime=getdate(),ModifiedBy='system'
Where jvid=@activeid
End
If(@oldrelativeid is null)
Begin
If(@newrelativeid is not null)
Begin
Print 'NO'
Print 'New Record insert'
select @relativeactiveid=client_activeid from client where client_ClientID=@newrelativeid
select @activeid=client_activeid from client where client_clientid=@clientid
Insert into
[PortfolioTracker].[DBO].JVIdFamilyMapping(Childjvid,jvid)
Values(@activeid,@relativeactiveid)
End
Else
Begin
Print'yes'
End
End
Else
If(@oldrelativeid is not null)
Begin
If(@newrelativeid is not null)
Begin
Print 'Replacing RelativeID'
Select @relativeactiveid=client_activeid from client where Client_clientid=@newrelativeid
select @activeid=client_activeid from client where client_clientid=@clientid
Update [PortfolioTracker].[DBO].JVIdFamilyMapping
Set jvid=@relativeactiveid where childjvid=@activeid
End
Else
If(@newrelativeid is null)
Begin
Print 'Deleting relative ID'
select @activeid=client_activeid from client where client_clientid=@clientid
Delete from [PortfolioTracker].[DBO].JVIdFamilyMapping where childjvid=@activeid
End
End
End

In the code the following part is not executing.
Basically it is else part of the IF condition. Can someone help me with this.

Begin
Print 'created date is not same as updateddate'
Select @activeid=client_activeid From client Where client_clientid=@clientid
Update [PortfolioTracker].[DBO].JVID_Master
Set password=@password,jvidname=@name,jvidemail=@email,rmcode=@rmcode,jvidmobileno=@phone,ModifiedDateTime=getdate(),ModifiedBy='system'
Where jvid=@activeid
End

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-28 : 08:58:46

moved from script library.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources