instead of trigger example

[joe]

hi i am trying to write a insted of insert trigger to
create a unique id when i insert a record in my database.
can anyone give me an example with out using identity. thanks

[Adam Machanic]

A few questions:

How will the ID be generated? And have you thought about how you will
ensure uniqueness without locking the entire table each time? This will
incur a severe performance penalty. But perhaps that doesn't matter in your
app.

Anyway, what's wrong with using IDENTITY for your needs, or at least to
assist?
"joe" <jc*****@hotmail.com> wrote in message
news:Xn*****************************@207.69.154.20 2...

hi i am trying to write a insted of insert trigger to
create a unique id when i insert a record in my database.
can anyone give me an example with out using identity. thanks

[joe]

i just read an article on google that talked about using instead of
triggers so i was a little bet puzzled. but i got it to work
i bet it needs locking but here is the code

CREATE TABLE table1 (

pk int NOT NULL ,

flag int NULL

)
create function maxpk()

returns int

as

BEGIN

DECLARE @qty INT

SELECT @qty = 0

select @qty=max(pk)+1 from table1

return ISNULL(@qty, 0)

END

CREATE TRIGGER tr_test ON table1

INSTEAD OF INSERT

AS BEGIN

INSERT INTO table1(pk,flag)

select dbo.maxpk(), flag from inserted

END

[Adam Machanic]

Does this code actually work for you? When I try an insert, I get the
error:

The column 'pk' in table 'table1' cannot be null.

If I make 'pk' nullable, another problem is exposed, that of multi-row
INSERTs; try this:

insert table1 (flag)
select 1
union all
select 1

You'll need a loop in the trigger to get rid of that problem, I believe.
"joe" <jc*****@hotmail.com> wrote in message
news:Xn*****************************@207.69.154.20 2...

i just read an article on google that talked about using instead of
triggers so i was a little bet puzzled. but i got it to work

[Erland Sommarskog]

joe (jc*****@hotmail.com) writes:

i just read an article on google that talked about using instead of
triggers so i was a little bet puzzled. but i got it to work
i bet it needs locking but here is the code

CREATE TABLE table1 (
pk int NOT NULL ,
flag int NULL
)
create function maxpk() returns int as
BEGIN
DECLARE @qty INT
SELECT @qty = 0
select @qty=max(pk)+1 from table1
return ISNULL(@qty, 0)
END

CREATE TRIGGER tr_test ON table1
INSTEAD OF INSERT
AS BEGIN
INSERT INTO table1(pk,flag)
select dbo.maxpk(), flag from inserted
END

Unfortunately, this won't fly well. It works if you insert one row at
a time, but as Adam pointed out, it fails for multi-row inserts. Also,
calling a user-defined function for each is bad for performance, because
the scalar UDF converts the statement to a cursor behind the scenes.
Again, this does not matter for a single-row insert, but if you insert
10000 rows, it will cost you dearly.

The simplest solution for this is to use - IDENTITY. But not on the
target table itself. Here's how you do it:
CREATE TABLE table1 (
pk int NOT NULL
CONSTRAINT default_pk DEFAULT 0,
flag int NULL,
CONSTRAINT pk_table1 PRIMARY KEY (pk)
)
go
CREATE TRIGGER tritest ON table1 INSTEAD OF INSERT AS

DECLARE @temp TABLE (ident int IDENTITY PRIMARY KEY,
flag int NULL)

DECLARE @maxpk int

INSERT @temp (flag)
SELECT flag FROM inserted

SELECT @maxpk = coalesce(MAX(pk), 0) FROM table1 WITH (UPDLOCK)

INSERT table1 (pk, flag)
SELECT @maxpk + ident, flag FROM @temp
go
INSERT table1 (flag)
SELECT OrderID FROM Northwind..Orders
go
SELECT * FROM table1
go
DROP TABLE table1
Of course, some people may ask why not use an IDENTITY column on
table1 directly? Well, assume that there is a business requirement
for a consecutive series of numbers, in which case IDENTITY won't do.

Then whether INSTEAD OF triggers is the way to go, I'm not really sure.
There is some extra maintenance job if you add more columns to the table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Adam Machanic]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Of course, some people may ask why not use an IDENTITY column on
table1 directly? Well, assume that there is a business requirement
for a consecutive series of numbers, in which case IDENTITY won't do.

I'm on a project right now where the requirement is to share surrogate
keys amongst three subtype tables that are all inherited from one supertype.
However, a much easier route was taken: Lock down the tables' write access
and only insert rows via a stored procedure. Personally, I find triggers
annoying to work with...

[Erland Sommarskog]

Adam Machanic (amachanic@hotmail._removetoemail_.com) writes:

I'm on a project right now where the requirement is to share surrogate
keys amongst three subtype tables that are all inherited from one
supertype. However, a much easier route was taken: Lock down the
tables' write access and only insert rows via a stored procedure.
Personally, I find triggers annoying to work with...

Yeah, for most of the tables where we have surrogate keys, we just
grab a MAX(id) in the insert stored procedure, and everything goes this
way. There are tables where we have IDENTITY, these mainly happens in the
table where the is an alternate natural key, but where need a surrogate
for some reason (for instance because the real key has one or more
nullable columns).

One problem with IDENTITY is that if you insert many rows at a time,
and you need to know the id value for each row, because you will also
insert rows in subtable.

Then we have these verification numbers that our system generates, but
which are not really surrogate keys - actually they are not always
even keys, and they are definite surrogates. They must be contiguous,
and also the different series must not collide. Here we have one one-
row, one-column table for each series.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Adam Machanic]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Then we have these verification numbers that our system generates, but
which are not really surrogate keys - actually they are not always
even keys, and they are definite surrogates. They must be contiguous,
and also the different series must not collide. Here we have one one-
row, one-column table for each series.

What are you using those to verify? That sounds like it would be
difficult to maintain (MUST be contiguous)...

[Erland Sommarskog]

Mischa Sandberg (mi*************@telus.net) writes:

The exact English term is 'serial number' --- because they must be in
SERIES.
Actually, I looked it up in my dictionary which suggested "voucher number".
And while we tend to use "verifikation" to mean some sort of transaction,
the name comes from the fact that there is a piece of paper somewhere.
A receipt, a contract note or similar. In older days, the piecee of papers
where pre-printed with the number on them.
An IDENTITY column is not guaranteed to be exact contiguous series,
even though under certain circumstances it's darn close.
As long as no inserts fails...
To determine that all transactions are there, and haven't been tampered
with, would probably better rely on digital signatures and a digest
checksum.

Way too easy to resequence an identity:

Nah, that's an overkill. As I mentioned there is paper all over the place.
If the auditor sits with a note or someother printout which says number
123456, and transaction 123456 is something else, he will get very very
concerned.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp