August 28, 2008 at 7:00 am
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,
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
August 28, 2008 at 7:20 am
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,
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
August 28, 2008 at 7:26 am
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.
August 28, 2008 at 8:14 am
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
August 28, 2008 at 8:35 am
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?
August 28, 2008 at 8:54 am
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"
August 28, 2008 at 8:59 am
Can you please verify what data type is used for ModifiedDateTime,CreatedDateTime columns?
August 28, 2008 at 9:07 am
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply