I need to add a trigger to a table to fire when an insert event happens.
Basically I need to update 2 columns (employee last name and first) from
another table. Something along these lines:
UPDATE dbo.test
SET dbo.test.lastname = dbo.employee.lastname,
dbo.test.firstname = dbo.employee.firstname
FROM dbo.test, dbo.employee
WHERE dbo.test.logonid = dbo.employee.account
any help is appreciated.Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:426824
On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:
>I need to add a trigger to a table to fire when an insert event happens.
>Basically I need to update 2 columns (employee last name and first) from
>another table. Something along these lines:
>UPDATE dbo.test
>SET dbo.test.lastname = dbo.employee.lastname,
>dbo.test.firstname = dbo.employee.firstname
>FROM dbo.test, dbo.employee
>WHERE dbo.test.logonid = dbo.employee.account
>any help is appreciated.
Hi Shane,
I'm not sure if I understand your requirements. It appears that you want
a trigger to copy information that is inserted into one table over to
another table. That is sually not a good idea: storing redundant data
wastes disk space, and (much more important!) introduces the risk of
getting data corruption - what if the two copies of the data are someday
not equal? Which of the two conflicting data sources should be
considered the "correct" source? And if source A is considered "correct"
in case of a conflict, what's the point of having source B?
But I might be wrong. Maybe I'm misunderstanding what you're trying to
do, or why you're trying to do it. If that's the case, then please write
back with the structure of your tables (posted as CREATE TABLE
statements, including all constaints and properties - though you may
omit irrelevant columns), some rows of sample starting data (posted as
INSERT statements), some typical INSERT statements that should fire the
trigger and the end result you expect after executing those statements
(i.e. the end result that the trigger should generate).
See www.aspfaq.com.5006 for more info on how to assemble the required
information.
Hugo Kornelis, SQL Server MVP|||essentially what would happen is a when a row was inserted into a table A, I
need a trigger to use the value in the employeeid column to locate the
employee record (based upon the employeeid) containing the lastname &
firstname columnsin table B and update the lastname and firstname columns i
n
the same row in table A.
"Hugo Kornelis" wrote:
> On Thu, 9 Mar 2006 12:00:17 -0800, Shane Faullin wrote:
>
> Hi Shane,
> I'm not sure if I understand your requirements. It appears that you want
> a trigger to copy information that is inserted into one table over to
> another table. That is sually not a good idea: storing redundant data
> wastes disk space, and (much more important!) introduces the risk of
> getting data corruption - what if the two copies of the data are someday
> not equal? Which of the two conflicting data sources should be
> considered the "correct" source? And if source A is considered "correct"
> in case of a conflict, what's the point of having source B?
> But I might be wrong. Maybe I'm misunderstanding what you're trying to
> do, or why you're trying to do it. If that's the case, then please write
> back with the structure of your tables (posted as CREATE TABLE
> statements, including all constaints and properties - though you may
> omit irrelevant columns), some rows of sample starting data (posted as
> INSERT statements), some typical INSERT statements that should fire the
> trigger and the end result you expect after executing those statements
> (i.e. the end result that the trigger should generate).
> See www.aspfaq.com.5006 for more info on how to assemble the required
> information.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Sun, 12 Mar 2006 14:13:27 -0800, Shane Faullin wrote:
>essentially what would happen is a when a row was inserted into a table A,
I
>need a trigger to use the value in the employeeid column to locate the
>employee record (based upon the employeeid) containing the lastname &
>firstname columnsin table B and update the lastname and firstname columns
in
>the same row in table A.
Hi Shane,
I see. So you want a default, but more complex than a standard DEFAULT
property has to offer.
I think you still should consider if you really need to redundantly
store the firstname and lastname in both tables. But here's a quick
attempt at the code:
CREATE TRIGGER MyTrigger
ON TableA
INSTEAD OF INSERT
AS
INSERT INTO TableA (EmployeeID, OtherColumns, FirstName, LastName)
SELECT i.EmployeeID, i.OtherColumns, e.FirstName, e.LastName
FROM inserted AS i
LEFT OUTER JOIN Employees AS e
ON e.EmployeeID = i.EmployeeID
go
Note that there's no error handling included. Also note that this code
is untested - see www.aspfaq.com.5006 if you prefer a tested reply.
Hugo Kornelis, SQL Server MVP|||Shane,
CREATE TRIGGER ON dbo.test
FOR INSERT
AS
SET NOCOUNT ON
UPDATE t
SET
t.lastname = e.lastname,
t.firstname = e.firstname
FROM dbo.test t, inserted i, dbo.employee e
WHERE t.logonid = i.logonid /* or whatever dbo.test's primary key is */
AND t.logonid = e.account
SET NOCOUNT OFF
GO
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Shane Faullin" <sfaullin@.jupitermed.com.jupiterflorida> wrote in message
news:A7952F6F-A5F0-4CDB-A6CD-B44A46267A77@.microsoft.com...
>I need to add a trigger to a table to fire when an insert event happens.
> Basically I need to update 2 columns (employee last name and first) from
> another table. Something along these lines:
> UPDATE dbo.test
> SET dbo.test.lastname = dbo.employee.lastname,
> dbo.test.firstname = dbo.employee.firstname
> FROM dbo.test, dbo.employee
> WHERE dbo.test.logonid = dbo.employee.account
> any help is appreciated.|||You can do a SELECT on the inserted virtual table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D6F26A0E-9667-4EC9-980E-26F935443749@.microsoft.com...
Hello,
My table has a uniqueidentifier with default value newid() and I need to
catch newly created uniqueidentifier in my insert trigger, how can I do
this?|||You can do a SELECT on the inserted virtual table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D6F26A0E-9667-4EC9-980E-26F935443749@.microsoft.com...
Hello,
My table has a uniqueidentifier with default value newid() and I need to
catch newly created uniqueidentifier in my insert trigger, how can I do
this?
