Else condition not validating in a trigger

  • 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

  • nithin.gujjar (8/28/2008)


    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

    There are a bunch of things wrong with that cursor. The main one is that it cannot handle multiple rows inserted in a single batch. Think about how your code will work if 10 rows are inserted. That alone could also be the cause of the ELSE not executing.

    As for that ELSE, put a print statement in the trigger so you can see what the actual value of that variable is to make SURE it is what you think it is.

    Also, since this is an INSERT only trigger, there will never be any rows in the DELETED table so it makes no sense to select from that table.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • The problem was that the trigger was for insert only , after adding update its working.

    However let me tell you that. This is for one record at a time ,its not for bulk insert. If you still think this is wrong please correct me.

  • Hey, if you think you can guarantee that only one row will ever be inserted/updated at a time then your trigger will be OK. The problem is that you simply cannot do this - unless you are the ONLY ONE that has access to the database using something other than your application. Anyone who can get in using QA/SSMS/sqlcmd can issue a statement that will affect more than one row in a single batch - and then you wind up with bad data.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Your ELSE condition should be executed only in very special conditions which are not related with the information in your "inserted" table.

    Omitting critics on trigger design...

    At the very beginning you are selecting a bunch of variables from inserted table.

    @activeid is one of these variables.

    Then you are verifying is @activeid is not null

    Then you are going to ELSE condition:

    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

    So, let me clarify. If @activeid selected from inserted IS NOT NULL, you are selecting yet another @activeid from the outside table and trying to update yet another outside table with variables selected from inserted based on condition of @activeid? If that is intenstion then it could be executed ONLY is the records with these @activeid exists in CLIENT and JVID_MASTER tables prior to main table.

    And again if that is an intension, you would have to verify that the data types for these column in all tables are the same...

    BTW, do you have any error in error log?

  • I can guarantee that only one record is inserted at a time because its a enterprise software which I am customizing, since it generates client_clientid by itself i can be sure that insertion will happen one at a time

    @activeid is null is a condition to check if the record is new or old record getting updated. If it is null then its a new record and I have to create a new active Id for the record using the algorithm.

    If @activeid is not null than i can use the active id for insertion or updation later.

    I also have a new problem

    Insert into

    PortfolioTracker.DBO.JVIdFamilyMapping(ModifiedDateTime,CreatedDateTime,JVId,ChildJVID,ModifiedBy,CreatedBy)

    Values (getdate(),getdate(),@relativeactiveid,@activeid,'system','system')

    this is throwing me this error

    "Conversion failed when converting datetime from character string"

  • Can you please verify what data type is used for ModifiedDateTime,CreatedDateTime columns?

  • The person who created the tables had made the createdby and modifiedby ad datetime so that was the problem.

    However I ak u guys for help if i still have other problems.

    Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply