another trigger question

I have a table named userInfo which has fields userID (uses Identity to fill
in), firstname, familyname.
I have a second table named administration which has fields userID and
userName.
I would like to have a trigger which when a new record is added to userInfo
creates a record in the administration table using the same userID and
joining the familyname and firstname fields from UserInfo together to make
the userName field in administration.
Could someone help me with the syntax.
Thank you
June
Why do you need an extra table for this? You can create a view called
vAdministration which calls:
SELECT userID, username = firstName + familyName FROM userInfo
No reason to store the data twice!
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"June Macleod" <junework@.hotmail.com> wrote in message
news:Oz734RpPEHA.2996@.TK2MSFTNGP12.phx.gbl...
> I have a table named userInfo which has fields userID (uses Identity to
fill
> in), firstname, familyname.
> I have a second table named administration which has fields userID and
> userName.
> I would like to have a trigger which when a new record is added to
userInfo
> creates a record in the administration table using the same userID and
> joining the familyname and firstname fields from UserInfo together to make
> the userName field in administration.
> Could someone help me with the syntax.
> Thank you
> June
>
|||On Thu, 20 May 2004 19:03:45 +0100, June Macleod wrote:

>I have a table named userInfo which has fields userID (uses Identity to fill
>in), firstname, familyname.
>I have a second table named administration which has fields userID and
>userName.
>I would like to have a trigger which when a new record is added to userInfo
>creates a record in the administration table using the same userID and
>joining the familyname and firstname fields from UserInfo together to make
>the userName field in administration.
>Could someone help me with the syntax.
>Thank you
>June
Hi June,
I could, but first I'll advice you to drop the administration table and
create an administration view instead:
CREATE VIEW administration AS
SELECT userID, familyname + ', ' + firstname AS userName
FROM userInfo
go
Another option would be to (again) drop the administration table and add
userName as computed column in the userInfo table:
ALTER TABLE userInfo
ADD userName AS familyname + ', ' + firstname
go
But if you really want to use seperate tables and keep it current with
triggers, you'll need not one but three triggers:
CREATE TRIGGER ins_userInfo
ON userInfo
AFTER INSERT
AS
IF @.@.ROWCOUNT > 0
INSERT administration (userID, userName)
SELECT userID, familyname + ', ' + firstname
FROM inserted
go
CREATE TRIGGER upd_userInfo
ON userInfo
AFTER UPDATE
AS
IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
UPDATE administration
SET userName = familyname + ', ' + firstname
WHERE userID IN (SELECT userID FROM inserted)
go
CREATE TRIGGER del_userInfo
ON userInfo
AFTER DELETE
AS
IF @.@.ROWCOUNT > 0
DELETE administration
WHERE userID IN (SELECT userID FROM deleted)
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> But if you really want to use seperate tables and keep it current with
> triggers, you'll need not one but three triggers:
Yes, excellent point... not only getting the data there on insert, but
keeping the tables in sync.
|||First of all let me thank you very much for your help. It is much
appreciated.
I have taken on board your advice about dropping the administration table
and using a view instead.
However, I am still trying to get the trigger to work as it will be good
practice for me for future tables which will require this type of update.
The insert and delete triggers work well however I am having problems with
the update trigger.
Create Trigger dbo.userInfo_Trigger1
On dbo.userInfo
AFTER UPDATE
AS
IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
UPDATE administration
SET userName = familyname + ', ' + firstname
WHERE userID IN (SELECT userID FROM inserted)
When I try to save the trigger it comes back with an error message "ADO
Error: Invalid column name 'familyname'. Invalid column name 'firstname'."
These are the correct names in the userInfo table.
I am making the assumption that the 'inserted' table (and likewise the
'deleted' table ) are temporary tables created during the edit process.
Does an inserted table get created when an update is taking place or only
when a new record is created?
Thanks again
June
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9o3qa0piuc0mm1j2lpvle020joi75glis4@.4ax.com...
> On Thu, 20 May 2004 19:03:45 +0100, June Macleod wrote:

> I could, but first I'll advice you to drop the administration table and
> create an administration view instead:
> CREATE VIEW administration AS
> SELECT userID, familyname + ', ' + firstname AS userName
> FROM userInfo
> go
> Another option would be to (again) drop the administration table and add
> userName as computed column in the userInfo table:
> ALTER TABLE userInfo
> ADD userName AS familyname + ', ' + firstname
> go
> But if you really want to use seperate tables and keep it current with
> triggers, you'll need not one but three triggers:
> CREATE TRIGGER ins_userInfo
> ON userInfo
> AFTER INSERT
> AS
> IF @.@.ROWCOUNT > 0
> INSERT administration (userID, userName)
> SELECT userID, familyname + ', ' + firstname
> FROM inserted
> go
> CREATE TRIGGER upd_userInfo
> ON userInfo
> AFTER UPDATE
> AS
> IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
> UPDATE administration
> SET userName = familyname + ', ' + firstname
> WHERE userID IN (SELECT userID FROM inserted)
> go
> CREATE TRIGGER del_userInfo
> ON userInfo
> AFTER DELETE
> AS
> IF @.@.ROWCOUNT > 0
> DELETE administration
> WHERE userID IN (SELECT userID FROM deleted)
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 21 May 2004 11:14:02 +0100, June Macleod wrote:
Hi June,

>First of all let me thank you very much for your help. It is much
>appreciated.
>I have taken on board your advice about dropping the administration table
>and using a view instead.
Good - glad to hear that.

>However, I am still trying to get the trigger to work as it will be good
>practice for me for future tables which will require this type of update.
That's why I went on to give you the trigger code after advising against
it.

>The insert and delete triggers work well however I am having problems with
>the update trigger.
>Create Trigger dbo.userInfo_Trigger1
>On dbo.userInfo
>AFTER UPDATE
>AS
>IF @.@.ROWCOUNT > 0 AND (UPDATE(familyname) OR UPDATE(firstname))
>UPDATE administration
>SET userName = familyname + ', ' + firstname
>WHERE userID IN (SELECT userID FROM inserted)
>When I try to save the trigger it comes back with an error message "ADO
>Error: Invalid column name 'familyname'. Invalid column name 'firstname'."
>These are the correct names in the userInfo table.
My fault. When I wrote that trigger, I momentarily forgot that userName is
not in the same table as familyname and firstname. The UPDATE statement
should read
UPDATE administration
SET userName = (SELECT familyname + ', ' + firstname
FROM inserted
WHERE inserted.userID = administration.userID)
WHERE userID IN (SELECT userID FROM inserted)

>I am making the assumption that the 'inserted' table (and likewise the
>'deleted' table ) are temporary tables created during the edit process.
Though technically incorrect, you might as well think of it that way. (The
exact technical explanation is that deleted and inserted are not temporary
tables, but pseudo-tables - they never really exist, but their contents
are reconstructed from the log file every time they are needed. If you
have a trigger that refers to inserted and deleted a lot, you might gain
performance by explicitly copying the data from those pseudo-tables to
temporary tables).

>Does an inserted table get created when an update is taking place or only
>when a new record is created?
First, it's important to note that the inserted and deleted pseudo-tables
can only be referenced inside a trigger. Even a stored procedure that is
called from a trigger has no access to inserted or deleted.
If a trigger is started as a result of an INSERT statement, than the
deleted pseudo-table will always be empty; the inserted pseudotable
contains all rows inserted by the INSERT statement.
If a trigger is started as a result of a DELETE statement, than the
deleted pseudo-table will contain all rows that are deleted by the DELETE
statement; the inserted pseudotable will be empty.
If a trigger is started as a result of an UPDATE statement, the deleted
pseudotable will contain all rows that match the WHERE clause of the
update, with all data as it was BEFORE the update; the inserted
pseudotable will contain the same rows as they appear AFTER applying the
SET clause of the update. If the UPDATE statement changed the value of the
primary key column(s) (which is unfortunately allowed in SQL Server), it
can be quite hard to find out which row in inserted matches which row in
deleted.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)