Sequence of number in a year without an auxiliary table
-
Hi,
I've this problem. I've a table to manage spedition with an identity column (id), a year field and a numberInYear field. The identity is the pk of the table and I want to make the numberInYear growing sequentially and restart every change year. I don't want to make a table to manage this sequence.
I thought this alghoritm:
1) Extract the max identity value (id) of the year before this, before the insert of the new record;
2) Insert the new record;
3) NumberInYear = the id just inserted minus the max id extract before.
The problem is the NumberInYear cannot be null, so I've to know the id of the record inserting before the inserting itself... Is it possible?
-
I'm sure there is a more efficient way and someone will probably post it, but to get started I would create and "After Insert" trigger. The trigger can first determine if this is the first record of the year and set the value to 1 if true, if false add 1 to the max value for current year and update the record. The initial insert can always put ZERO in the column which the trigger would use to know it is supposed to replace the ZERO with a valid number. This would also allow inserts that WANT to set the number to do so without being changed by the trigger. If you would like an example for the trigger post the DDL for the table and I (or someone) will post the actual trigger code.
James.
-
Ok, below is the script for the table...
The field of interest are:
ID_Spedizione
AA_Anno
Val_NumSpedizione
The last is the number to create...
Tnx
----------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_GSP_Spedizione_GSP_Spedizione1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[GSP_Spedizione] DROP CONSTRAINT FK_GSP_Spedizione_GSP_Spedizione1
GO
/****** Object: Table [dbo].[GSP_Spedizione] Script Date: 29/08/2007 15.55.05 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GSP_Spedizione]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GSP_Spedizione]
GO
/****** Object: Table [dbo].[GSP_Spedizione] Script Date: 29/08/2007 15.55.07 ******/
CREATE TABLE [dbo].[GSP_Spedizione] (
[ID_Spedizione] [int] IDENTITY (1, 1) NOT NULL ,
[AA_Anno] [smallint] NOT NULL ,
[Val_NumSpedizione] [smallint] NOT NULL ,
[ID_Mittente] [smallint] NOT NULL ,
[Cod_UORichiedente] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Des_ResponsabileRichiedente] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dat_Inserimento] [datetime] NOT NULL ,
[ID_TipoSpedizione] [tinyint] NULL ,
[ID_TipoContenuto] [smallint] NULL ,
[Des_Contenuto] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Txt_Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID_Status] [tinyint] NOT NULL ,
[Cod_MatricolaGestore] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dat_PresaInCarico] [datetime] NULL ,
[Loc_MailRichiedente] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-
Try this:
Create trigger tri_gsp_spedizione on gsp_spedizione for Insert as
begin
declare @LastVal int
if exists (select 1 from inserted where val_numspedizione = 0)
begin
select @LastVal = Coalesce(Max(g.val_numspedizione),0)
from gsp_spedizione g join inserted i on (g.aa_anno = i.aa_anno)
where g.id_spedizione <> i.id_spedizione
update gsp_spedizione
set gsp_spedizione.val_numspedizione = @LastVal + 1
from gsp_spedizione g join inserted i on (g.id_spedizione = i.id_spedizione)
end
end
go
NOTE: This will only work for single record inserts, if you will be doing multi-record inserts such as (insert into ... select from) then the trigger will need to be tweaked.
James.
-
Change the algorithm.
When inserting, check if there is any record for same year. if there is, just add +1 to maximum NumberInYear for that year.
If a record is not found, just put 1.
This is because you may insert records out-of chronological order.
N 56°04'39.16"
E 12°55'05.25" -
DECLARE
@Sample TABLE (ID INT IDENTITY, yyyy INT, NumberInYear int)
select
* from @sample
declare
@year int
set
@year = 1996
insert
@sample (yyyy, numberinyear)
select
@year,
isnull((select max(numberinyear) from @sample where yyyy = @year), 1)
select
* from @sample
insert
@sample (yyyy, numberinyear)
select
@year,
isnull((select 1 + max(numberinyear) from @sample where yyyy = @year), 1)
select
* from @sample
set
@year = 1997
insert
@sample (yyyy, numberinyear)
select
@year,
isnull((select 1 + max(numberinyear) from @sample where yyyy = @year), 1)
select
* from @sample
set
@year = 1996
insert
@sample (yyyy, numberinyear)
select
@year,
isnull((select 1 + max(numberinyear) from @sample where yyyy = @year), 1)
select
* from @sample
N 56°04'39.16"
E 12°55'05.25" -
I think the solution of James is almost the right one...
Some explanation:
"if exists (select 1 from inserted where val_numspedizione = 0)"
is to prevent the trigger executio if the insert was wrong?
"g.id_spedizione i.id_spedizione"
is necessary to "eliminate" the record just inserted, right?
Now, probably I'll use an INSERT INTO, so what tweak dou you mean?? I'm new with triggers..
@peter-2: I'm sure that the cronological oreder is respected
-
the "If Exists" check is to only perform the "increment update" if the incomming value is zero. This would allow a manual insert of a non-zero value such as 1000 in the val_numspedizione column if for some reason you suddenly wanted to jump that number up (the trigger would then increment from new MAX value for all other inserts that have a zero value in that field).
Yes the "g.id_spedizione <> i.id_spedizione" is the eliminate the "just inserted" record, though not really necessary because even if it considered it, it has a zero value and would not adversly affect the calculation.
The tweak is to make the the trigger handle a "multiple rows" of data in the "inserted" table. Give me a few minutes and I'll rework it.
James.
-
This may be what is wanted:
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE TRIGGER TR_I_GSP_Spedizione
ON dbo.GSP_Spedizione
AFTER INSERT
AS
DECLARE @I TABLE
(
ID_Spedizione int NOT NULL
,AA_Anno smallint NOT NULL
,RowID int IDENTITY NOT NULL
)
INSERT INTO @I (ID_Spedizione, AA_Anno)
SELECT ID_Spedizione, AA_Anno
FROM inserted
ORDER BY AA_Anno, ID_Spedizione
OPTION (MAXDOP 1)
UPDATE S
SET Val_NumSpedizione = I.RowID - D1.MinID + 1 + D2.MaxVal
FROM dbo.GSP_Spedizione S
JOIN @I I
ON S.ID_Spedizione = I.ID_Spedizione
JOIN (
SELECT S2.AA_Anno
,ISNULL(MAX(S2.Val_NumSpedizione), 0) AS MaxVal
FROM dbo.GSP_Spedizione S2 WITH (UPDLOCK)
GROUP BY S2.AA_Anno
) D2
ON S.AA_Anno = D2.AA_Anno
JOIN (
SELECT I1.AA_Anno
,MIN(I1.RowID) AS MinID
FROM @I I1
GROUP BY I1.AA_Anno
) D1
ON S.AA_Anno = D1.AA_Anno
GO
-
Nice! That works great. Now I don't have to think as hard
Since it is completely set based there is no need to check for the ZERO value in the incremented column, if it is not zero and larger then any existing value it will be used as the starting "max" value.
Only thing to note is that you can not have an alternate key assigned to the Year/Number columns since you need to initially assign duplicate values to the Number column when inserting. Just something for the OP to keep in mind.
James.
-
Oops! You do need to check for the non-zero value otherwise you end up over writting that value with a +1. add the following where clause to the insert into portion of the code:
INSERT INTO @I (ID_Spedizione, AA_Anno)
SELECT ID_Spedizione, AA_Anno
FROM inserted
WHERE [Val_NumSpedizione] = 0
ORDER BY AA_Anno, ID_Spedizione
OPTION (MAXDOP 1)
James.
-
Tnx to all!!
I'll never reach it without you... Probably only the single trigger, but the trigger for more than one insert is... awesome!! Tnx again.
Just last two questions:
1) I'll put the insert code in a SP with inside Transaction. If it goes in error the trigger is not execute, right? And if the trigger goes in error (for some reason...), the transaction is rollbacked??
-
Yes the trigger executes within the transaction of the original insert, so any error that prevents the "Commit" statement from running results in all work being rolledback.
You can easily test with this statement:
begin transaction
insert into gsp_spedizione (aa_anno,val_numspedizione)
select 2007,0 union all
select 2007,0 union all
select 2007,0 union all
select 2008,0 union all
select 2007,0 union all
select 2006,0 union all
select 2007,1000 union all
select 2006,0 union all
select 2008,0 union all
select 2007,0
go
select * from gsp_spedizione
go
rollback
select * from gsp_spedizione
The first select will show all the rows inserted and the second would not show them. NOTE: I abreviated the insert statements for testing (my table only had the three columns you were interested in).
James.
-
>>>And if the trigger goes in error (for some reason...), the transaction is rollbacked??
Maybe.
You need to be careful with transactions. If SET XACT_ABORT is off, as it normally is, then the statement is terminated but you have to check @@ERROR and do the ROLLBACK yourself. IF SET XACT_ABORT is on then the transaction will automatically be rolled back.
-
>>>>have to check @@ERROR
I know, i'm using always this.
Tnx to all.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply