Inserting Record into table

I'm working on an app that when a record is added in Table1, that id needs
to be populated into Table2 and Table3. When a user enters a new record, a
process would have to check table2 and 3 for the existence of that id. I
searched the net and didn't have any luck? I think a trigger would work to
kick off the process but not sure how to create the procedure to search for
the existence of a record id.
Any help would be appreciated.
thanks
- rob
hi Rob,
"Rob" <temp@.dstek.com> ha scritto nel messaggio
news:uW730eC8EHA.4072@.TK2MSFTNGP10.phx.gbl
> I'm working on an app that when a record is added in Table1, that id
> needs to be populated into Table2 and Table3. When a user enters a
> new record, a process would have to check table2 and 3 for the
> existence of that id. I searched the net and didn't have any luck?
> I think a trigger would work to kick off the process but not sure how
> to create the procedure to search for the existence of a record id.
> Any help would be appreciated.
> thanks
> - rob
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE tableA (
ID INT NOT NULL PRIMARY KEY ,
Name VARCHAR(10) NOT NULL ,
Country VARCHAR(10) NOT NULL
)
CREATE TABLE tableB (
ID INT NOT NULL PRIMARY KEY ,
Name VARCHAR(10) NOT NULL ,
Country VARCHAR(10) NOT NULL
)
CREATE TABLE tableC (
ID INT NOT NULL PRIMARY KEY ,
Name VARCHAR(10) NOT NULL ,
Country VARCHAR(10) NOT NULL
)
GO
CREATE TRIGGER tr_I_tableA ON tableA
FOR INSERT
AS BEGIN
IF @.@.ROWCOUNT = 0 RETURN
INSERT INTO tableB SELECT i.ID , i.Name , i.Country
FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableB b WHERE
b.ID = i.ID)
INSERT INTO tableC SELECT i.ID , i.Name , i.Country
FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableC c WHERE
c.ID = i.ID)
END
GO
INSERT INTO tableB VALUES ( 1 , 'xxx', 'xxx' ) -- this row will not be
overwritten in tableB
INSERT INTO tableC VALUES ( 2 , 'xxx', 'xxx' ) -- this row will not be
overwritten in tableC
INSERT INTO tableA VALUES ( 1 , 'Andrea', 'Italy' )
INSERT INTO tableA VALUES ( 2 , 'Rob', 'USA' )
PRINT 'tableB'
SELECT * FROM tableB
PRINT 'tableC'
SELECT * FROM tableC
GO
DROP TABLE tableC, tableB, tableA
--<--
tableB
ID Name Country
-- -- --
1 xxx xxx
2 Rob USA
tableC
ID Name Country
-- -- --
1 Andrea Italy
2 xxx xxx
have a wonderfull new year
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks ... this is great !!!!
- rob
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:33o9duF40p561U1@.individual.net...
> hi Rob,
> "Rob" <temp@.dstek.com> ha scritto nel messaggio
> news:uW730eC8EHA.4072@.TK2MSFTNGP10.phx.gbl
> SET NOCOUNT ON
> USE tempdb
> GO
> CREATE TABLE tableA (
> ID INT NOT NULL PRIMARY KEY ,
> Name VARCHAR(10) NOT NULL ,
> Country VARCHAR(10) NOT NULL
> )
> CREATE TABLE tableB (
> ID INT NOT NULL PRIMARY KEY ,
> Name VARCHAR(10) NOT NULL ,
> Country VARCHAR(10) NOT NULL
> )
> CREATE TABLE tableC (
> ID INT NOT NULL PRIMARY KEY ,
> Name VARCHAR(10) NOT NULL ,
> Country VARCHAR(10) NOT NULL
> )
> GO
> CREATE TRIGGER tr_I_tableA ON tableA
> FOR INSERT
> AS BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> INSERT INTO tableB SELECT i.ID , i.Name , i.Country
> FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableB b WHERE
> b.ID = i.ID)
> INSERT INTO tableC SELECT i.ID , i.Name , i.Country
> FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableC c WHERE
> c.ID = i.ID)
> END
> GO
> INSERT INTO tableB VALUES ( 1 , 'xxx', 'xxx' ) -- this row will not be
> overwritten in tableB
> INSERT INTO tableC VALUES ( 2 , 'xxx', 'xxx' ) -- this row will not be
> overwritten in tableC
> INSERT INTO tableA VALUES ( 1 , 'Andrea', 'Italy' )
> INSERT INTO tableA VALUES ( 2 , 'Rob', 'USA' )
> PRINT 'tableB'
> SELECT * FROM tableB
> PRINT 'tableC'
> SELECT * FROM tableC
> GO
> DROP TABLE tableC, tableB, tableA
> --<--
> tableB
> ID Name Country
> -- -- --
> 1 xxx xxx
> 2 Rob USA
> tableC
> ID Name Country
> -- -- --
> 1 Andrea Italy
> 2 xxx xxx
> have a wonderfull new year
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>