Re: User Functions in batch inserts
From: oj (nospam_ojngo_at_home.com)
Date: 02/25/04
- Next message: Uri Dimant: "Re: Querying Data"
- Previous message: mountain man: "Re: aba_lockinfo - new version available"
- In reply to: TJoker .NET: "Re: User Functions in batch inserts"
- Next in thread: TJoker .NET: "Re: User Functions in batch inserts"
- Reply: TJoker .NET: "Re: User Functions in batch inserts"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 25 Feb 2004 00:12:51 -0800
Hi,
Sorry for the late reply...Here is a quick example of instead of trigger...
create table t(i int primary key, j uniqueidentifier)
go
create trigger _tr on t
instead of insert
as
if @@rowcount=0 return
declare @i int
select @i=isnull(max(i),0) from t
select i=identity(int,1,1),j
into #tmp
from inserted
insert t
select i+@i,j
from #tmp
go
insert t
select 999,newid()
union all select 999,newid()
union all select 999,newid()
union all select 999,newid()
union all select 999,newid()
union all select 999,newid()
union all select 999,newid()
union all select 999,newid()
union all select 999,newid()
go
select * from t
go
drop table t
-- -oj http://www.rac4sql.net "TJoker .NET" <nonono@nononono.no> wrote in message news:093d01c3fb07$e95d8930$a501280a@phx.gbl... > Thanks. But how would the trigger know the value to be > used ? How would such trigger look like ? > > Thanks for helping. > > TJ! > > -- > TJoker > MVP: Paint, Notepad, Solitaire > > **************************************** > > > >-----Original Message----- > >That's because the inserting data has not been committed > yet. You will want > >to use Instead Of Trigger for this. > > > >Really, why can't you use Identity property. > > > >-- > >-oj > >http://www.rac4sql.net > > > > > >"TJoker .NET" <nonono@nononono.no> wrote in message > >news:08f401c3faf7$d66e8820$a301280a@phx.gbl... > >> > >> I'm having some trouble with an Insert statement. > >> Here are my table definifions: > >> > >> table: test_id > >> rowId -> INT - NOT NULL > >> name -> CHAR(10) - NOT NULL > >> date -> DATETIME - NOT NULL > >> uid -> UNIQUEIDENTIFIER - NOT NULL > >> > >> table: test_id_source > >> name -> CHAR(10) > >> > >> I populated the test_id_source table with a bunch of > rows > >> of sample data. > >> > >> In the test_id table, the rowId column cannot be an > >> identity column but it has a similar purpose of being > the > >> PK of the table. > >> I removed the PK constraint for this test. > >> > >> The value for the rowId, when inserting a new row, comes > >> from the following user function: > >> > >> CREATE FUNCTION GetNextTestId() RETURNS INT > >> AS > >> BEGIN > >> DECLARE @Res INT > >> SELECT @Res=1 + ISNULL(MAX(rowId), 0) FROM test_id > >> RETURN @Res > >> END > >> > >> So, if I do a simple statement like the following it > works > >> fine: > >> INSERT INTO test_id ( rowid, [name], [date], uid ) > >> VALUES (dbo.GetNextTestId(), 'some data', getdate(), > >> newid()) > >> > >> My problem is during the folowing batch insert: > >> > >> INSERT INTO test_id ( rowid, [name], [date], uid ) > >> SELECT dbo.GetNextTestId(), [name], getdate(), newid > () > >> FROM test_id_source > >> > >> I got something like this: > >> > >> [rowId][name] [date] [uid] > >> 31 a 2004-02-24 10:27:01.523 73ED52BC- > >> BADF-42AF-B5F4-140E15D914B2 > >> 31 b 2004-02-24 10:27:01.523 A6D6D13F- > >> 7CB2-4328-80EA-8E16E1B2BC8B > >> 31 c 2004-02-24 10:27:01.523 0B04F89A- > >> 5866-45FF-9B0E-A376FFC8F615 > >> 31 d 2004-02-24 10:27:01.523 4322486A- > >> 4F5E-4918-AE68-F5CD1C60E492 > >> 31 e 2004-02-24 10:27:01.523 A4F52511- > >> F13C-4D2E-9B81-618F8C062538 > >> 31 f 2004-02-24 10:27:01.523 4E877AA2- > >> A1A4-42A5-A07A-AA36DFD6EC0A > >> > >> So, the NEWID() function was evaluated once for each > row, > >> but my function was not! > >> I wish it was numbered 31, 32, 33 .. etc .. > >> Is this the correct behavior? Is there another way of > >> doing this without adding extra columns to > >> the table (this is a production database, so I really > >> can't mess with it a lot) > >> > >> Thanks a lot > >> > >> TJ! > >> > >> -- > >> TJoker > >> MVP: Paint, Notepad, Solitaire > >> > >> **************************************** > >> > >> > > > > > >. > >
- Next message: Uri Dimant: "Re: Querying Data"
- Previous message: mountain man: "Re: aba_lockinfo - new version available"
- In reply to: TJoker .NET: "Re: User Functions in batch inserts"
- Next in thread: TJoker .NET: "Re: User Functions in batch inserts"
- Reply: TJoker .NET: "Re: User Functions in batch inserts"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
