October 6, 2011 at 10:16 pm
hi,
I want to create a trigger for.. if i insert any rows into my table..my primary key column want to increase by one..my primary key column is not the identity column..so i insert any records in to my table..my trigger want to insert max(account_sk)+1 in to my primary_key column
how to write trigger for this
my table structure
account_sk account_id account_name
1 100 deposit
2 120 assit
3 129 ...
October 6, 2011 at 11:24 pm
as per your requirement, i think you can achieve this through function, why you are tring to use trigger can you explain a bit about your logic.
create a udf where u get the max value of the account_sk add 1 and return the value,
u call this function where you are inserting the value to your table.
it would look like this:-
create function dbo.udf_getnextaccoutvalue()
returns bigint
as
begin
return(select MAX(account_sk)+1 from table1)
end
and while inserting into your table
insert into table1(account_sk,account_id,account_name)
select dbo.udf_getnextaccoutvalue(),1,'account'
hope it helps
October 6, 2011 at 11:30 pm
thanks a lot..
my scenario..
i copied all tables from oracle db to sql db.. in oralce db by using trigger,
sequence auto generate to insert date to table..but in sql server there no sequence so by using trigger only i want to increase my primary_key data+1..
so my boss told to me create trigger to generate identity data ..
October 6, 2011 at 11:45 pm
can you please little bit more explainotry in your problem the amount of information you are providing is not sufficient for us to understand the problem,
few points we need some explanation.
you want to increment your primary key column.(whats the use definitly you want to insert the incremented value to the table)
by create a trigger you can increment the no. but you cann't use it while inserting your data to table, as the calling of that is not in your hand.
and how you will go to insert into the same table. via the trigger which is part of the same table.
your idea make me scratch my head.
follow the link to how to post question in forums.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 6, 2011 at 11:57 pm
hey i create trigger like this
CREATE TRIGGER [dim_geography_trigger]
ON dbo.dim_GEOGRAPHY
AFTER INSERT
AS
DECLARE @LW INT
SELECT @LW =MAX(GEOGRAPHY_sk)
FROM dbo.dim_GEOGRAPHY
UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1
this trigger will update all GEOGRAPHY_sk =GEOGRAPHY_sk+1 but i want to update only last inserted record GEOGRAPHY_sk = max(GEOGRAPHY_sk)+1..
how can i do this..
my table stucture
GEOGRAPHY_sk GEOGRAPHY_id country
10 111 india
11 190 japan
12 3 london
so if i insert any records into my table my GEOGRAPHY_sk will increment by 1..
October 7, 2011 at 12:18 am
Do you know SQL server has a feature called identity? All you need to do is define your primary key column as an int column and give it the identity(<seed>,<increment>) attribute, forget about triggers at all. Have a look at books online 'create table' statement: link.
Something like this:
create table dbo.MyTable (
table_id int identity( 1, 1) not null,
table_column varchar(100) not null,
constraint PK_MyTable primary key (table_id)
);
October 7, 2011 at 12:20 am
in your updated you can mention where clause with your inserted value of geography_id,country but you have to be sure that combination of these two is unique
if it's not then it will update the other row also
UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1
from dim_geography g inner join inserted i
on g.GEOGRAPHY_id =i.GEOGRAPHY_id
and g.country=i.country
or the other way might be you can try if your geography_id is null but u have to make sure that only the latest row inserted is only null if it is having other
rows as null it will update that too.
UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1
from dim_geography
where geography_id is null
you have to be sure that u put condition in the update which will only update the latest row either of the one query you can use. its depend which one is met your reqirement
October 7, 2011 at 12:21 am
hi,
By using trigger only i want increment by one..i wont use identity in my sk column
October 7, 2011 at 12:25 am
hey i create trigger like this
CREATE TRIGGER [dim_geography_trigger]
ON dbo.dim_GEOGRAPHY
AFTER INSERT
AS
DECLARE @LW INT
SELECT @LW =MAX(GEOGRAPHY_sk)
FROM dbo.dim_GEOGRAPHY
UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1
this trigger will update all GEOGRAPHY_sk =GEOGRAPHY_sk+1 but i want to update only last inserted record GEOGRAPHY_sk = max(GEOGRAPHY_sk)+1..
how can i do this..
my table stucture
GEOGRAPHY_sk GEOGRAPHY_id country
10 111 india
11 190 japan
12 3 london
so if i insert any records into my table my GEOGRAPHY_sk will increment by 1.
October 7, 2011 at 12:37 am
CREATE TRIGGER [LW_NO] ON dbo.dim_GEOGRAPHY
AFTER INSERT
AS
DECLARE @LW INT
SELECT @LW =MAX(GEOGRAPHY_sk)
FROM dbo.dim_GEOGRAPHY
UPDATE dbo.dim_GEOGRAPHY
SET GEOGRAPHY_sk = @LW + 1
from dim_geography
where geography_sk is null
ya friend so thanks i got that..thanks
October 7, 2011 at 12:50 am
its glad that you got your solution, but using identity column is better then doing all this.
if can redesign your table it would be better as per performance and maintainability prospective
October 7, 2011 at 1:07 am
oh ok thanks..
shall i want to know..
how to alter a numeric column to identity in sql server
October 7, 2011 at 1:30 am
Just use the "alter table" command.
For example:
alter table dbo.Mytable alter column colname int identity(1,1) not null
October 7, 2011 at 1:58 am
As Lumbago showed you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166392
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
October 7, 2011 at 4:55 am
ya that question in sql server forums..post by me only
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply