Link to home
Create AccountLog in
Avatar of jdana
jdanaFlag for United States of America

asked on

TSQL Trigger Assistance Need

Take a look at the image.  

In Example 1, the trigger needs to recognize that the next number in the sequence for 2010 values is 4.  
In Example 2, the trigger needs to recognize that the there are no 2011 numbers and start over at 1.

Please provide some TSQL suggestions.

Thanks  

Trigger-Example.jpg
Avatar of Aneesh
Aneesh
Flag of Canada image

is this an assignment question ?
Avatar of jdana

ASKER

Nope it's an honest piece of work.  (The actual "Number" value is currently at 16,211, but I figured I simplify it for readability.)
Please post the structure of your table (CREATE TABLE).
So you are saying the Number value is 16,211, when does it "WRAP" to increment year?  In the new year?  So is this basically a rolling counter per year?
you need to give us the current trigger ...

and explain what you actually want to happen...

and the significance of the sequence...

e.g. just to get you thinking  
what do you desire if an update occurs which resets a sequence number ...
are you trying to fill gaps...
what do you want to happen if the max is reached  ,, (fail or go negative?)
ASKER CERTIFIED SOLUTION
Avatar of simonpaul64
simonpaul64

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jdana

ASKER

Here's the structure of the table.  (I've omitted constraints and indices for the sake of brevity.)

The two columns on which I need the trigger are WorkOrderYear and WorkOrderNumber
/****** Object:  Table [dbo].[WorkOrder]    Script Date: 10/05/2010 ******/
PRINT 'CREATING TABLE: [WorkOrder]'
CREATE TABLE [dbo].[WorkOrder](
	[WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
	[WorkOrderYear] int NULL,
	[WorkOrderNumber] int NULL,
	[StartDate] [smalldatetime] NULL,
	[ParkID] [int] NULL,
	[WorkGroupID] [int] NULL,
	[TotalCost] [money] NULL,
	[AddedUser] [varchar] (15) NULL,
	[AddedDate] [smalldatetime] NULL,
	[ModifiedUser] [varchar] (15) NULL,
	[ModifiedDate] [smalldatetime] NULL,
	[wdWOMainGeneralOID] [int] NULL,
 CONSTRAINT [PK_WorkOrder] PRIMARY KEY CLUSTERED 
(
	[WorkOrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

Avatar of jdana

ASKER

simonpaul64,

The max number for the current year stuff looks good.  It's the second half of the trigger that's got me flummoxed.  How do I pass the new WorkOrderNumber value to the WorkOrderNumber column while in the middle of an INSERT process?  Yipes.