trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trigger

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-03 : 05:45:15

Hi

i am having table like this

stu_id stu_name class insert_id
1 john A 1
2 reena A 2
3 thinu B 3
4 scott A 4

i wrote trigger for gerating insert_id

create TRIGGER trg_student
ON student_details
AFTER INSERT
AS
DECLARE @LW INT
SELECT @LW =ISNULL(MAX(insert_id),0)
FROM student_details
UPDATE student_details
SET insert_id=@LW + 1
from student_details
where insert_id is null

but i need
stu_id stu_name class insert_id
1 john A 1
2 reena A 2
3 thinu B 1
4 scott A 3
5 tiger B 2
6 smith B 3

when my class change my insert_id starts from 1 and increment by +1
but my trigger will give only sequence how to write tsql for this

anand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-03 : 05:49:25

don't need trigger at all. Just do it in your select statement


select stu_id, stu_name, class, insert_id = row_number() over ( partition by class order by stu_id)
from   student_details

KH
[spoiler]Time is always against us[/spoiler]

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-03 : 05:58:59

ya thanks for ur reply..
but u want to understand my scenario
when i try to insert data into my table that insert_id column data will be populated based on trigger

ex,insert into stu_details values(1,'john','A')
my insert_id will populated by trigger
so my table look like
stu_id stu_name class insert_id
1 john A 1

again
insert into stu_details values(1,'reena','A')

stu_id stu_name class insert_id
1 john A 1
2 reena A 2

then
insert into stu_details values(1,'thinu','B')

stu_id stu_name class insert_id
1 john A 1
2 reena A 2
3 thinu B 1

this d way i want to write tsql

anand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-03 : 06:00:47

what i am trying to say is, you don't need the insert_id in the table at all. You can generate that number during a select statement

KH
[spoiler]Time is always against us[/spoiler]

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-12-03 : 06:08:36

shall u plz tell how to write tsql-trigger for this

anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 07:32:02

quote:
Originally posted by Anand.A

shall u plz tell how to write tsql-trigger for this

anand

There's no need of trigger which is what Tan has been telling you for long. you just need to use row_number() function as he showed you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-03 : 07:43:35

What is the use of this insert_id column ?

You don't really required this insert_id column in your table. You can use row_number() to generate it during select statement. And since no column is required, you don't need to write the trigger at all.

KH
[spoiler]Time is always against us[/spoiler]

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-03 : 09:23:05

Yes, I also agree with all, Rather than keeping insert_id column and writing trigger. You can add datetime column called DateCreated. Whenever a record is inserted in your table just add server/system date time to this column.

Now, when you select records from this table just do select row_number() over (partition by student id order by DateCreated),* from your_table where class='A' order by DateCreated

Above is a raw query you might get some syntax error but you can do google for correct syntax.

Above query will give you records in correct order of insertion due to DAteCreated column.

Hope you got what i mean.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 12:51:48

quote:
Originally posted by jassi.singh

Yes, I also agree with all, Rather than keeping insert_id column and writing trigger. You can add datetime column called DateCreated. Whenever a record is inserted in your table just add server/system date time to this column.

Now, when you select records from this table just do select row_number() over (partition by student id order by DateCreated),* from your_table where class='A' order by DateCreated

Above is a raw query you might get some syntax error but you can do google for correct syntax.

Above query will give you records in correct order of insertion due to DAteCreated column.

Hope you got what i mean.

Which query are you referring to?
All posted queries will work without any error as long as you're on SQL 2005 and above with compatibility level over 90

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources