[quoted text, click to view]

There are several issues here. First, your trigger design would only
work if a single row is updated at a time. Second, assuming that your
table columns are nvarchar, then ASCII() may not work in all cases, as
it cannot handle Unicode data - you would need UNICODE(). Third, it's
not clear why you need this sort order column at all - I guess these
two queries would give you the same output:

select *
from tblPeoplesNames
order by bintSortOrder

select *
from tblPeoplesNames
order by FirstName, LastName

Finally, it's not usually a good idea to put data types in column and
variable names. If you change them, you have to change all your code,
and since SQL requires all data types to be explicitly declared,
there's little value in putting them into identifiers.

Having said all that, I don't know your requirements, so if you really
need to do this via a trigger, then one possible UPDATE statement
could be something like this:

update tblPeoplesNames
set SortOrder = cast(
convert(varchar, unicode(i.FirstName)) +
convert(varchar, unicode(substring(i.FirstName, 2, 1)) +
convert(varchar, unicode(i.FamilyName)) +
convert(varchar, unicode(substring(i.FamilyName, 2, 1))
as int)
from tblPeoplesNames t join inserted i
on t.PrimaryKeyColumn = i.PrimaryKeyColumn

Hi all

Just coming to grips with triggers and t-sql.

I am trying to build a trigger to calculate a sorting code in a table

so far I have gotten :-

================================================================
CREATE TRIGGER [MakeSortCode] ON dbo.tblPeoplesNames
FOR UPDATE
AS

-- Calculates a numeric sort order, from the first two letters of the
-- person first and family name

declare @intSortCode as integer,
@chrLetter1 as varchar(1),
@chrLetter2 as varchar(1),
@chrLetter3 as varchar(1),
@chrLetter4 as varchar(1)

DECLARE @bintSortOrder as integer

if UPDATE (nvarcharFirstName) or UPDATE (nvarcharFamilyName)

-- if an update has taken place on the name fields
BEGIN
select @chrLetter1 = left((SELECT nvarcharFirstName FROM Inserted),1)
select @chrLetter2 = substrING((SELECT nvarcharFirstName FROM
Inserted),2,1)

select @chrLetter3 = left((SELECT nvarcharFamilyName FROM Inserted),1)
select @chrLetter4 = substrING((SELECT nvarcharFamilyName FROM
Inserted),2,1)

select @bintSortOrder = (ascii(@chrLetter1) * 10000)
select @bintSortOrder = @bintSortOrder +( ascii(@chrLetter2) * 100)
select @bintSortOrder = @bintSortOrder + (ascii(@chrLetter3) * 10)
select @bintSortOrder = @bintSortOrder + (ascii(@chrLetter4) )

-- THIS NEXT STEP HAS ME CONFUSED ?????????????????????
INSERT INTO tblPeoplesNames
bintSortOrder
(SELECT bintSortOrder = @bintSortOrdeR FROM inserted ins)

END

RETURN
=============================================

The bit that has me baffled is how to put the calculated value back into the
record that was just updated.

I have a simple understanding that an update causes two tables to be created
(one with the old values, one with the new) but how to implement them is
proving hard to fathom

All suggestions gratefully accepted

Cheers

Ray

[quoted text, click to view]

Generally yes, because you don't know in advance how many rows a client will
update - it could be one, it could be 100. And since triggers normally
enforce data integrity and business rules, you want to make sure they act on
all the data. Triggers in MSSQL always fire per-statement, not per-row
(which I believe is the case in some other products).

[quoted text, click to view]
code.

At the end of the day, a naming convention is just that, and as long as it
works for you and is consistent, then it's fine. Using data types in
identifiers is just a rather 'un-SQL' way of doing things, although judging
by newsgroup posts, it's not uncommon.

[quoted text, click to view]

If you want to know the data type of a column, you can use sp_help or query
the system views (INFORMATION_SCHEMA.COLUMNS in this case). As I understand
it, the advantage of putting the data type in a variable name in other
languages is that the data type may vary during execution or, even if it's
fixed, it may not be immediately clear. Since data types are always declared
in SQL, and metadata is stored in system tables for reference, this isn't
really an issue.

[quoted text, click to view]

As above, this is rarely useful - if you explain exactly what you're trying
to achieve by a per-row trigger, then someone may be able to suggest an
alternative solution.

[quoted text, click to view]

Thanks Simon

Thats some very good ideas there.

[quoted text, click to view]

Yes, thats what I plan to do. Is that unusual?

[quoted text, click to view]
Great point, I am not even sure why I made them nVarchar. I will need to
research that more.

[quoted text, click to view]
The reasons for the sort code are a little bit more complicated than this
sample. Later, other things like postal codes, job descriptions etc are
planned to come into play.

[quoted text, click to view]
I was using suggestions from several "how to" books, plus tha habits of many
years in other languages.
I would have thought that changing the variable names would be the least of
your problems is you changed data types, like an entire review of all code.

[quoted text, click to view]
Only so you know the column types when you write code for them surely ?

[quoted text, click to view]

Thats a good example, but I was hoping to use a trigger for each record. I
guess I will have to persevere a bit more.

Many thanks for the advice

Ray

Hi Simon

Thanks for the further comments. I appreciate the time taken

[quoted text, click to view]

Yes, during a little more research,, I believe that Oracle works per row.
The comment "how many rows a client will update" is the one that intrigues
me. Surely the trigger fires when any ONE record is updated. So if multiple
rows are updated individually , the trigger fires each time.
I may be missing something here, but how could a client update more than one
row, without the trigger being fired except say, with a general purpose
UPDATE query making bulk changes
something like (excuse the primitive code)

UPDATE tblenames AGE WITH 0;
GO

That seems like a prime candidate for a trigger on the tblnames to have to
cater for multiple rows.
But the concept I have in mind is modifying single parent or related records
using some sort of GUI, where the trigger should fire after avery row
update.

If my logic is flawed, I would be interested in getting a different
perspective, thats for sure.

[quoted text, click to view]

Actually, I would have thought it was the more common approach, based on the
methodology in all the third party books I have read, but I wouldnt press
the point until I get a bit more expertise in the product myself.
[quoted text, click to view]

Hmmm, yes, its always possible to find out variable types in a lot of ways,
but for maintenance and rapid understanding by other coders, I would have
thought that naming variables to rapidly identify and remember their data
types in a complicated SP beats having to jump to another 2 or 3 interfaces
to look up the variable type. It might be different for the original
developer, but as we know, the maintenance is a much bigger issue.
I will need to be convinced that naming variables isnt a quicker way at this
stage.
[quoted text, click to view]

Thats a good point. If I had to think of why I assumed the trigger would and
should fire at each record update, I think the strategy is that it makes the
newly calculated data available to other users instantly. If I assumed the
trigger wouldnt fire until say 10 records had been updated, I would need to
make provision for reports or user inquiries to test how many records were
awaiting the calculation.
If the sort code affected order of deliveries, or picking of stock etc in a
big application, 10 un-updated orders , may be significant in my mind.
Any other takes on my thinking would be greatfully appreciated.

Many thanks for the points raised though, much apreciated.

Ray Watson (RW@IINET.NET.AU) writes:
[quoted text, click to view]

Yes. The trigger fires once per statement.

[quoted text, click to view]

To take one example:

UPDATE employees
SET salary = 1.02 * salary

Everyone gets a 2% salary raise. And if there is trigger, it fires exactly
once.

[quoted text, click to view]

Yes, that is what you think now. Six months later, your requirements
changes, and users asks for being able to insert or update multiple
entries, and you tear your head, because you don't understand why the
trigger no longer works.

It's all about writing robust code.

Occasionally, it is virtually impossible to handle multiple-row updates
(one example is updates of the primary key). In this case you should
something like this in the trigger:

IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
RAISERROR('Multi-row updates not permitted, 16, -1)
ROLLBACK TRANSACTION
RETURN
END

[quoted text, click to view]

As long as the compiler does not enforce the name convention, you
cannot trust it anyway. This my main grip about Hungarian notation.
Well, it's noisy too.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at