trigger not doing anything

[roddie]

Hi. I am working on a small project and I am using Java with a SQL Server 2000 back-end. I have written a trigger on one of the database tables which apparently is not doing anything.
Given below is the T-SQL code

CREATE TRIGGER [insertPreviousO wner] ON [dbo].[ITEquipmentMast er]
FOR UPDATE
AS
declare @previousOwner int
declare @prevOwner1 int
declare @prevOwner2 int
declare @prevOwner3 int
declare @prevOwner4 int
declare @prevOwner5 int
declare @serial varchar

set @previousOwner = (select currentOwner from deleted)
set @prevOwner1=(se lect previousOwner1 from deleted)
set @prevOwner2=(se lect previousOwner2 from deleted)
set @prevOwner3=(se lect previousOwner3 from deleted)
set @prevOwner4=(se lect previousOwner4 from deleted)
set @prevOwner5=(se lect previousOwner5 from deleted)
set @serial=(select serialNum from inserted)

if (@prevOwner1 is null)
begin
update ITEquipmentMast er set previousOwner1= @previousOwner where serialNum= @serial
end
else if (@prevOwner1 is not null and @prevOwner2 is null)
begin
update ITEquipmentMast er set previousOwner2= @previousOwner where serialNum= @serial
end
else if (@prevOwner2 is not null and @prevOwner3 is null)
begin
update ITEquipmentMast er set previousOwner3= @previousOwner where serialNum= @serial
end
else if (@prevOwner3 is not null and @prevOwner4 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end
else if (@prevOwner4 is not null and @prevOwner5 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end

For any update, it's supposed to check for a null previousOwner1, previousOwner2, previousOwner3, previousOwner4 or previousOwner5 field for the updated row. It should then insert the updated row's currentOwner value into the null previousOwner field.
Can anyone assist?

[ck9663]

Hi. I am working on a small project and I am using Java with a SQL Server 2000 back-end. I have written a trigger on one of the database tables which apparently is not doing anything.
Given below is the T-SQL code

CREATE TRIGGER [insertPreviousO wner] ON [dbo].[ITEquipmentMast er]
FOR UPDATE
AS
declare @previousOwner int
declare @prevOwner1 int
declare @prevOwner2 int
declare @prevOwner3 int
declare @prevOwner4 int
declare @prevOwner5 int
declare @serial varchar

set @previousOwner = (select currentOwner from deleted)
set @prevOwner1=(se lect previousOwner1 from deleted)
set @prevOwner2=(se lect previousOwner2 from deleted)
set @prevOwner3=(se lect previousOwner3 from deleted)
set @prevOwner4=(se lect previousOwner4 from deleted)
set @prevOwner5=(se lect previousOwner5 from deleted)
set @serial=(select serialNum from inserted)

if (@prevOwner1 is null)
begin
update ITEquipmentMast er set previousOwner1= @previousOwner where serialNum= @serial
end
else if (@prevOwner1 is not null and @prevOwner2 is null)
begin
update ITEquipmentMast er set previousOwner2= @previousOwner where serialNum= @serial
end
else if (@prevOwner2 is not null and @prevOwner3 is null)
begin
update ITEquipmentMast er set previousOwner3= @previousOwner where serialNum= @serial
end
else if (@prevOwner3 is not null and @prevOwner4 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end
else if (@prevOwner4 is not null and @prevOwner5 is null)
begin
update ITEquipmentMast er set previousOwner4= @previousOwner where serialNum= @serial
end

For any update, it's supposed to check for a null previousOwner1, previousOwner2, previousOwner3, previousOwner4 or previousOwner5 field for the updated row. It should then insert the updated row's currentOwner value into the null previousOwner field.
Can anyone assist?

Just by reading, it looks like you're trigger is recursing. Check the db setting (sp_dboption). You have an update trigger on ITEquipmentMast er and inside that trigger, you're updating it. It's also called nested triggers. Do you really have to follow this design/structure?

-- CK

[roddie]

Just by reading, it looks like you're trigger is recursing. Check the db setting (sp_dboption). You have an update trigger on ITEquipmentMast er and inside that trigger, you're updating it. It's also called nested triggers. Do you really have to follow this design/structure?

-- CK

Thanx CK. Have discovered that the problem lied in the declaration
declare @serial varchar which was returning a single character only. Needed to specify the length of the @serial variable. Once again, thanks

[roddie]

Hi everyone.I developed a small java application. I am using jasper reports api for my reporting (exporting them to pdf). My problem is that when I install the application on another client and run it, the reports don’t come out. Can anyone please assist?

[ck9663]

Tons of reasons. Is everything working aside from the report? Can your apps connect to db?

-- CK