Creating a trigger

Hi all,
I've never played with triggers before but I need to create one, so I guess
I need some help!!
I've got a table TABLE1 in DATABASE1 and it has 2 fields MYID and MYNAME.
This is where the trigger needs to be created. MYID refers to a field in
another table in another database.
I need a trigger to update (on insert only) MYNAME according to what MYID is
by looking into the other table and retrieving the value.
Should be easy for the gurus in here!!
Any ideas?
Thanks,
IvanUntested:
USE Database1
GO
CREATE TRIGGER trg_table1 ON Table1
FOR INSERT
AS
UPDATE Table1
SET myname =
(SELECT myname
FROM database2.dbo.table1 AS T
WHERE myid = Table1.myid)
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE myid = Table1.myid)
Perhaps a better option though would be to create a view instead:
CREATE VIEW table1_with_name
(myid,myname)
AS
SELECT T1.myid, COALESCE(T1.myname,T2.myname)
FROM database1.dbo.Table1 AS T1
LEFT JOIN database2.dbo.Table1 AS T2
ON T1.myid = T2.myid
Either way, it can be useful to indirect all external database
references through views because it reduces the number of things that
need changing if you decide to relocate a database.
David Portas
SQL Server MVP
--|||Try,
create trigger dbo.tr_table1 on dbo.table1
for insert
as
set nocount on
update dbo.table1
set myname = (select a.myname from database2..tablex as a where a.myid =
table1.myid)
where exists(select * from inserted as i where i.myid = table1.myid)
AMB
"Ivan Debono" wrote:

> Hi all,
> I've never played with triggers before but I need to create one, so I gues
s
> I need some help!!
> I've got a table TABLE1 in DATABASE1 and it has 2 fields MYID and MYNAME.
> This is where the trigger needs to be created. MYID refers to a field in
> another table in another database.
> I need a trigger to update (on insert only) MYNAME according to what MYID
is
> by looking into the other table and retrieving the value.
> Should be easy for the gurus in here!!
> Any ideas?
> Thanks,
> Ivan
>
>|||Thanks :)
Ivan
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1114524112.314396.275370@.g14g2000cwa.googlegroups.com...
> Untested:
> USE Database1
> GO
> CREATE TRIGGER trg_table1 ON Table1
> FOR INSERT
> AS
> UPDATE Table1
> SET myname =
> (SELECT myname
> FROM database2.dbo.table1 AS T
> WHERE myid = Table1.myid)
> WHERE EXISTS
> (SELECT *
> FROM Inserted
> WHERE myid = Table1.myid)
> Perhaps a better option though would be to create a view instead:
> CREATE VIEW table1_with_name
> (myid,myname)
> AS
> SELECT T1.myid, COALESCE(T1.myname,T2.myname)
> FROM database1.dbo.Table1 AS T1
> LEFT JOIN database2.dbo.Table1 AS T2
> ON T1.myid = T2.myid
> Either way, it can be useful to indirect all external database
> references through views because it reduces the number of things that
> need changing if you decide to relocate a database.
> --
> David Portas
> SQL Server MVP
> --
>