Hi all,

I am trying to create a Card Number for clients, after inserting a new
record, based on a clientID field
and the year part of the date:

CardNo = Year(Date) + PadL(ClientID, 7, '0')

PADL is an UDF to left PAD a string.

I wrote the following trigger, but I get an error when execute it:

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++
USE [MyDB]
GO
/****** Object: Trigger [dbo].[tr_makeCardNo] Script Date: 01/04/2010
19:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tr_makeCardNo]
ON [dbo].[ClientData]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE ClientData
SET CardNo = STR(DATEPART(yyyy,C.DataEntryDate)) +
dbo.PADL(C.ClientID,7,'0')
FROM ClientData C
JOIN Inserted I ON C.ClientID = I.ClientID
END
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++
This trigger works if I use either of the 2 strings but not when I
concatenate them as above.
I get an error about truncating a binary or string variable and the insert
is aborted but
THE IDENTITY FIELD IS INCREMENTED NONTHELESS!

Any help will be appreciated.

The STR function returns CHAR data type, but default 10 characters. You can use the 'length' argument of the function to
specify 4:
STR(DATEPART(yyyy, C.DataEntryDate), 4)

Or simply cast to CHAR(4):

CAST(DATEPART(yyyy, C.DataEntryDate) AS CHAR(4))

--
Plamen Ratchev
http://www.SQLStudio.com

That was it. Thanks very much.

Now I need generate a barcode based on this card number and store it in the
database.
Is there a way to this using a trigger?

"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote

Cypher wrote:
What does the function look like?


I used the following code to try and reproduce your problem but it ran
without error. What are you doing differently?

USE Test
GO
/****** Object: Trigger [dbo].[tr_makeCardNo] Script Date:
01/04/2010
19:07:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE ClientData (
ClientID int IDENTITY,
CardNo varchar(50) NULL,
DataEntryDate datetime NOt NULL)

GO
create function dbo.PADL(@str varchar(20),@final_length tinyint,
@padwith char(1))
returns varchar(256)
AS
BEGIN
return right(replicate(@padwith,@final_length) + @str, @final_length)
END
GO

CREATE TRIGGER [dbo].[tr_makeCardNo]
ON [dbo].[ClientData]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE ClientData
SET CardNo = STR(DATEPART(yyyy,C.DataEntryDate)) +
dbo.PADL(C.ClientID,7,'0')
FROM ClientData C
JOIN Inserted I ON C.ClientID = I.ClientID
END
GO

insert ClientData(DataEntryDate) values(getdate());
select * from ClientData


--
HTH,
Bob Barrows

Hi Bob,

Thanks for your response.
The reason it worked for you and not for me is that my CardNo field is
char(11)
while yours is varchar(50).

regards

"Bob Barrows" <reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote

Depends on what barcode you need to generate. If you can write the logic in T-SQL then not a problem to use it in a
trigger. I am not an expert on barcodes so cannot offer any T-SQL code. A quick search shows something that may be of help:
http://devshed.us/Blogs/tabid/227/En...-in-T-SQL.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

Cypher (cypher (AT) cypher (DOT) com) writes:
Yes, an IDENTITY value is consumed no matter whether the statement
succeeds or not. If you need to have consecutive numbers, you should
not use IDENTITY.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Why did you store a computed column in a table? SQL is a declarative
language. But you are using procedural code all over the place, in a
TRIGGER and a UDF! You are still thinking as if you were writing for
magnetic tape files or punch cards. You even use the word "record"
because your mindset is all wrong.

There is no such thing as an IDENTITY field. First of all, a column
is nothing like a field. It is a proprietary, non-relational table
property. Programmers who do not know RDBMS often use it to get a
"record number" so that they can make their SQL look like a
sequential file -- magnetic tape files or punch cards.

The DDL skeleton would be something like this:

CREATE TABLE Clients
(client_id CHAR(7) NOT NULL PRIMARY KEY
CHECK(client_id LIKE '<pattern>'),
signup_date DATE DEFAULT CURRENT_DATE NOT NULL,
(<build card number here>) AS card_nbr,
etc);

You need a way of getting a client identifier that can be validated
and verified. IDENTITY has neither and it cannot even give you
consecutive numbers. I would not expose the client id in the card
number -- do you write your PIN on your bank card?

You need to learn how to follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Thanks, I needed that! How did you know I am old enough to have used punched
cards on a DEC System 10? But seriously:
- I need the computed field in there because another dumb ID card printing
program will use it to print an ID card. Also it is a foreign key field in
other
tables that contain data about the client. The id card is used later to
retrieve
data using either a barcode or a Mag card reader.
- I use the term IDENTITY because that is what MS calls an auto-increment
field.
- Consecutive numbers are not a requirement, but after the testing is done
and
the database is on a production server, I expect it will be. I was somewhat
surprised by the SQL Server behavior because I expected the operation
to be rolled back on error.
- This is not my day job but I am beginning to like it :-)

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

"Some" programmers do, not a lot.

"Most" programmers use it to create a stable (none changing) surrogate key
that can be used to aid concurrency, consistency and all those things I keep
telling you - read Date.

That reminds me - the left/right pair of the nested sets model you
popularise cannot be "validated" and "verified" because on day 1 the
left/right pair might be 12,1 and the next day or even the next minute it
might be 12,5; so, any table using the left/right pairing is now
disconnected and orphaned.

So, my point - people in glass houses should not throw stones!

The stable and unchangeable value returned by IDENTITY on insert can easily
be verified back - once inserted the value can never change.

Gaps occur only on rollbacks but the benefits in concurrency massively out
weigh this problem; the architect needs to balance that with a "real" need
for an incrementor without gaps in which case they must destroy concurrency
and use MAX( x ) + 1 with some severe locking.

--ROGGIE--

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote