Execute

[sRockyHill]

Hi,

I need to execute this tsql code:

UPDATE IssueTable SET IssueNo = (IssueNo)-(1);
WHERE (CaseID = ?)

This could be a trigger or something else...

When a case worker delete a Issue Number = 1, we need to reset the IssueNo to 1, 2, 3. We have to have sequence 1, 2, 3...
They can delete any IssueNo, but after it deletes, sequence has to reset. When They delete a IssueNo from IssueTable they delete with a CaseId. We are using a MS Sql server 2000. This need to be done on the server.

Can you help?

Thank you

 

[JohnDTampaBay]

Assuming only one row is deleted at a time, this should do the trick:

CREATE TRIGGER UpdateIssueNo
ON [dbo].[IssueTable]
AFTER DELETE AS
UPDATE it
SET IssueNo = (IssueNo)-(1)
FROM dbo.IssueTable AS it
INNER JOIN Deleted AS del
ON it.CaseID = del.CaseID
WHERE it.IssueNo > del.IssueNo

For multi-row deletes, the trigger will have to be a little more complex. For instance, will you ever need to delete an entire CaseID?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[sRockyHill]

They will be deleting 1 IssueNo at a time.

I get an error message:

Error 209: Ambiguous column name 'IssueNo'


CREATE TRIGGER UpdateIssueNo
ON [dbo].[IssueTable]
AFTER DELETE AS
UPDATE it
SET IssueNo = (IssueNo)-(1)
FROM dbo.IssueTable AS it
INNER JOIN Deleted AS del
ON it.CaseID = del.CaseID
WHERE it.IssueNo > del.IssueNo





Actual tabale:


CREATE TABLE [IssueTable] (
[pk] [int] IDENTITY (1, 1) NOT NULL ,
[IssueNo] [int] NOT NULL ,
[CaseID] [int] NOT NULL ,
[IssueDate] [smalldatetime] NULL ,
[RollUp] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CallType] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CallReason] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Outcome] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suboutcome] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CallReason_Subcode] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_IssueTable] PRIMARY KEY CLUSTERED
(
[pk]
) ON [PRIMARY]
) ON [PRIMARY]
GO

 

[sRockyHill]

I think it works fine now.

Thank you

 

[JohnDTampaBay]

Oops! Missed an alias.

CREATE TRIGGER UpdateIssueNo
ON [dbo].[IssueTable]
AFTER DELETE AS
UPDATE it
SET IssueNo = (it.IssueNo)-(1)
FROM dbo.IssueTable AS it
INNER JOIN Deleted AS del
ON it.CaseID = del.CaseID
WHERE it.IssueNo > del.IssueNo

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)