sequence company code dependent through insert triggers

Question

Post reply

sequence company code dependent through insert triggers

aspardeshi

SSC Eights!

Points: 886
More actions
October 16, 2010 at 1:03 am

#388022

Hello,
I have a following tables:-
Company Master
co int
coname varchar(50)
Account Master
co int
accid int
acname varchar(50) pk co,accid fk co reference companymaster(co)
Now I have to insert sequence for accid column in account master, but the account id should be in sequence depending on the company i.e co column. so if co is 1 and the next sequence is 5 then 5 should come, if the company login id is 2 and the next sequence for company no 2 is 11 then 11 should come.
I have tried following scenario
as account master is pk co,accid it is a required column. how to generate sequence in account master accid column in an insert trigger. The user cannot input this column.
i am a complete noob in sql server 2005. So please write your sample trigger so that i can copy and paste in sql server management studio and see.
Please note: do not tell me to use identity as the identity will only generate sequence and not sequence per company. I have tried identity and it is working but of no use to me in this scenario, so please suggest what can be done from an insert trigger only. 🙂
Thank you in advance professionals !
I was very good at sql but variety spoiled me ! :w00t:

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

First, create the tables and some initial data. YOU SHOULD HAVE PROVIDED THIS!

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

if object_id('dbo.AccountMaster','U') IS NOT NULL DROP TABLE dbo.AccountMaster;

if object_id('dbo.CompanyMaster','U') IS NOT NULL DROP TABLE dbo.CompanyMaster;

CREATE TABLE dbo.CompanyMaster (co int PRIMARY KEY CLUSTERED, coname varchar(50));

CREATE TABLE dbo.AccountMaster (

co int,

accid int,

acname varchar(50),

PRIMARY KEY CLUSTERED( co,accid));

ALTER TABLE dbo.AccountMaster

ADD CONSTRAINT FK_AM_to_CM FOREIGN KEY (co)

REFERENCES dbo.CompanyMaster(co);

INSERT INTO dbo.CompanyMaster

SELECT 1, '1' UNION ALL

SELECT 2, '2' UNION ALL

SELECT 3, '3' UNION ALL

SELECT 4, '4';

INSERT INTO dbo.AccountMaster

SELECT 1, 1, '1-1' UNION ALL

SELECT 1, 2, '1-2' UNION ALL

SELECT 1, 3, '1-3' UNION ALL -- next should be 1-4

SELECT 2, 1, '2-1' UNION ALL

SELECT 2, 2, '2-2' UNION ALL -- next should be 2-3

SELECT 3, 1, '3-1' UNION ALL -- next should be 3-2

SELECT 4, 1, '4-1' UNION ALL

SELECT 4, 2, '4-2' UNION ALL

SELECT 4, 3, '4-3' UNION ALL

SELECT 4, 4, '4-4' UNION ALL

SELECT 4, 5, '4-5'; -- next should be 4-6

-- see what is already there

select * from dbo.CompanyMaster;

select * from dbo.AccountMaster;

Now, make the trigger:

CREATE TRIGGER dbo.AM_Insert ON dbo.AccountMaster

INSTEAD OF INSERT

AS

WITH CTE_Base AS

(

SELECT co, accid = max(accid)

FROM dbo.AccountMaster

GROUP BY co

)

INSERT INTO dbo.AccountMaster(co, accid, acname)

SELECT i.co,

acctid = c1.accid + ROW_NUMBER() OVER (PARTITION BY i.co ORDER BY acname),

acname

FROM inserted i

JOIN CTE_Base c1

ON i.co = c1.co;

GO

Finally, test it out:

select * from dbo.AccountMaster;

insert into dbo.AccountMaster(co, acname)

select 1, 'New for company 1' UNION ALL

select 2, 'New for company 2' UNION ALL

select 3, 'New for company 3' UNION ALL

select 4, 'New for company 4' UNION ALL

select 1, 'New for company 1' UNION ALL

select 2, 'New for company 2' UNION ALL

select 3, 'New for company 3' UNION ALL

select 4, 'New for company 4' ;

select * from dbo.AccountMaster;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

aspardeshi SSC Eights! Points: 886 More actions · Answer 2

Hi wayne,

Actually i already have a insert trigger for other processing, shall i create a different trigger in addition to my existing insert trigger, i have done so, but it is giving error for co column even though the co column value is there it is saying that co cannot be blank !

I am stumped ! Please help.

I was very good at sql but variety spoiled me ! :w00t:

aspardeshi SSC Eights! Points: 886 More actions · Answer 3

to make the forum simpler I have given you another simpler example in my first post, actually it is an example of student master. so instead of account master use student master and student id.

here goes my existing student master insert trigger.

ALTER trigger [dbo].[studentmstins] on [dbo].[college_studentmaster] after insert AS

DECLARE @SWV_NEW_CO VARCHAR(255)

DECLARE @SWV_NEW_COLLEGECD VARCHAR(255)

DECLARE @SWV_NEW_STUDENTID VARCHAR(255)

DECLARE @SWV_NEW_PARENTSCHOSENUSERNAME VARCHAR(255)

DECLARE @SWV_NEW_EMAILID VARCHAR(255)

DECLARE @SWV_NEW_COURSECD VARCHAR(255)

DECLARE @SWV_NEW_CLASSCD VARCHAR(255)

DECLARE @SWV_NEW_DIVISION VARCHAR(255)

DECLARE @SWV_NEW_YEARCD VARCHAR(255)

DECLARE @PASSWOR VARCHAR(10)

DECLARE @SWV_Cursor_For_NEW CURSOR

SET @SWV_Cursor_For_NEW = CURSOR FOR SELECT co, collegecd, studentid, parentschosenusername, emailid, coursecd, classcd, division, yearcd FROM inserted

OPEN @SWV_Cursor_For_NEW

FETCH NEXT FROM @SWV_Cursor_For_NEW INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_STUDENTID,@SWV_NEW_PARENTSCHOSENUSERNAME,

@SWV_NEW_EMAILID,@SWV_NEW_COURSECD,@SWV_NEW_CLASSCD,@SWV_NEW_DIVISION,

@SWV_NEW_YEARCD

WHILE @@FETCH_STATUS = 0

begin

DECLARE @c1 CURSOR

DECLARE C2 cursor FOR select @SWV_NEW_STUDENTID from college_studentmaster

DECLARE @vstudentid INT

insert into college_ugmembers(username,groupid) values(@SWV_NEW_PARENTSCHOSENUSERNAME,2)

exec dbo.GenerateRandomString 1,1,1,null,10,@PASSWOR OUT

insert into college_users(co,collegecd,saasuser,saaspass,emailid,active,usergroup,studentid) values(@SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_PARENTSCHOSENUSERNAME,@passwor ,@SWV_NEW_EMAILID,1,2,@vstudentid)

insert into college_parentsmaster(co,collegecd,studentid,parentusername) values(@SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_STUDENTID,@SWV_NEW_PARENTSCHOSENUSERNAME)

insert into college_studentmasterparents select * from college_studentmaster where

co = @SWV_NEW_CO and collegecd = @SWV_NEW_COLLEGECD and coursecd = @SWV_NEW_COURSECD

and classcd = @SWV_NEW_CLASSCD and division = @SWV_NEW_DIVISION and studentid = @SWV_NEW_STUDENTID

and yearcd = @SWV_NEW_YEARCD

close @c1

FETCH NEXT FROM @SWV_Cursor_For_NEW INTO @SWV_NEW_CO,@SWV_NEW_COLLEGECD,@SWV_NEW_STUDENTID,@SWV_NEW_PARENTSCHOSENUSERNAME,

@SWV_NEW_EMAILID,@SWV_NEW_COURSECD,@SWV_NEW_CLASSCD,@SWV_NEW_DIVISION,

@SWV_NEW_YEARCD

end

CLOSE @SWV_Cursor_For_NEW

I want to generate student id co column wise, Now can you guide me where to add your code here ! and how ! 😎

I was very good at sql but variety spoiled me ! :w00t:

WayneS SSC Guru Points: 95460 More actions · Answer 4

What college and professor gave you this homework assignment?

Why did you try to obscure your homework assignment by posting this as a company/account issue?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

aspardeshi SSC Eights! Points: 886 More actions · Answer 5

Hello Wayne,

I am sorry dear, that was totally unintentional and happened casually, I just put the case as I found simpler. I am a noob in sql server 2005. I knew there can be many insert triggers, so I didn't want to make it complicated for everyone in the forum to understand, I thought it will resolve the issue by putting another trigger.

Please do not misunderstand me, I hope so. Please guide me.

Thank you.

I was very good at sql but variety spoiled me ! :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please answer Wayne's question

WayneS (10/16/2010)
What college and professor gave you this homework assignment?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aspardeshi SSC Eights! Points: 886 More actions · Answer 7

Greetings !

I am a freelance programmer in php and sql server. previously I designed ERP solutions in oracle and they are successfully running for past 15 years, but sql server 2005 is just getting on my nerves. Now there comes numerous times that people need help, So there are various linkages, it is not always possible to mention every linkage (not required often) which can mess up the question to a great degree of extent. So I tried to put it in the simplest form for all to read and understand. May be it was converted, I agree my mistake, but there was nothing to hide. I have put several of my work on this forums openly.

I hope you all do not misunderstand me, and help me in future. 🙂

BTW, Every member in this forum, I have got this problem resolved. by making studentid identity column and providing seperate column for schools/colleges for entering student number. It is working fine now.

Thank you in advance professionals for your help extended in doing so. you are the real metal professionals which I do not have any doubt on.

I was very good at sql but variety spoiled me ! :w00t:

Paul White SSC Guru Points: 150467 More actions · Answer 8

SQL Server does not have Sequences in quite the same way as Oracle.

There is a workaround, not sure if it will help you though:

http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

aspardeshi SSC Eights! Points: 886 More actions · Answer 9

Paul White NZ (10/18/2010)
SQL Server does not have Sequences in quite the same way as Oracle.
There is a workaround, not sure if it will help you though:
http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx

Thanks Paul, for your great article ! You rock ! 🙂

I was very good at sql but variety spoiled me ! :w00t:

Paul White SSC Guru Points: 150467 More actions · Answer 10

aspardeshi (10/18/2010)
Thanks Paul, for your great article ! You rock ! 🙂

You're welcome. Good luck with the SQL Server work, I can appreciate it must be frustrating coming from Oracle - I would struggle making the reverse transition, I'm sure 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi