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
> --
>

creating a blank verion of the database

Hi all,
I need to create a blank version of my database. That is, all tabes are
empty ready for input. But also copy acroos all the triggers and sp's. This
on the same SQL server.
The purpose, is that I neede a completely fresh start for testing purposes
with different departments
Whats the best way to achieve this.
Thanks
RobertOne way is to generate DDL script from a database:
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:OCT9QLMWGHA.3800@.TK2MSFTNGP03.phx.gbl...[
color=darkred]
> Hi all,
> I need to create a blank version of my database. That is, all tabes are
> empty ready for input. But also copy acroos all the triggers and sp's. Thi
s
> on the same SQL server.
> The purpose, is that I neede a completely fresh start for testing purposes
> with different departments
> Whats the best way to achieve this.
> Thanks
> Robert
>[/color]|||if you're on SQL 2000, right click on your database, select all tasks
-> generate SQL script, then click show all, then go nuts with the
checkboxes, tick everything. This should give you a script that when
you run it will create a new database (so you'll have to change the
name it creates the db as if you're using the same server
Cheers
Will|||Script the database and execute the script for another database name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Obtain the full script for all the object bearing on mind the collation and
go on.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Jens" wrote:

> Script the database and execute the script for another database name.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Createing New Triggers in SQL Server Management Studio Express

Is it possible to create a new trigger for a SQL 2000 Database using SQL Server Management Studio Express?

hi,

yes...

connect to the desired instance.. select the required database and navigate to the "table" node.. expand the desired table/view object and select the Triggers node.. right click and write your trigger.. a template is presented as well..

or just open a new query window, select the desired database and type the trigger code..

regards