Update a table from another - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update a table from another

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-09-03 : 17:34:02

I'm new to this so bear with me.

I have a two databases in SQL server, version 7 I think, but being upgraded to 2000 soon.

The first one (staff) is accessed by other members of staff via forms in an Access database.

What I need, is when a new member of staff is added or a current one is updated is for that corresponding record to be either added to or updated in the other table (accounts).

From what I've read, a trigger is what I need to use, is this correct?

If so, can anybody provide me with an example based on the above scenario as I haven't got a clue where to begin.

Thanks in advance,

Mark

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-03 : 17:47:07

create trigger trinsstaff on staff for insert
as
insert accounts
select staffid, ...
from inserted
go
create trigger trupdstaff on staff for update
as
update accounts
set ... = ,,,
from inserted
where inserted.staffid = accounts.staffid
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-09-03 : 18:06:33

Thanks for that nr, I'll give that a go at work tomorrow

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-09-04 : 04:14:12

OK, I've been "playing about" with triggers and managed to get something very basic working. Here's what I'm finally after:

I have two tables, the first one is called Table1 and it's in the database called personnel. The second table, called Table2, is in a database called LSP.

What I want is when a new member of staff is added to Tabel1, some of they're details are used to create a new record in Table2 in the LSP.

Out of Table1 I want to extract NINo and place it into the same field in Table2, which is easy enough. What I need is to set a bit field called PermissionsUpdated to 0 in Table2 at the same time.

How do I do this?

Thanks for any help.

Mark

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-04 : 05:38:39

Just include an update statement in the trigger after the insert.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-09-04 : 09:01:34

Call me thick but I'm finding this very confusing!!

Here's what I have and can't get too work:

CREATE TRIGGER trig_AddToTest ON [dbo].[Users]
FOR INSERT
AS
INSERT Test SELECT UserID FROM Inserted
UPDATE Test SET PermissionsUpdated = 0 FROM Inserted WHERE Inserted.UserID = [dbo].[Users].UserID

PermissionsUpdated is a bit field, I'm trying to set it to false.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-04 : 09:14:35

create table Test (UserID int, PermissionsUpdated bit default 1)
go
CREATE TRIGGER trig_AddToTest ON [dbo].[Users]
FOR INSERT
AS
INSERT Test (UserID) SELECT UserID FROM Inserted
UPDATE Test SET PermissionsUpdated = 0 FROM Inserted WHERE Inserted.UserID = Test.UserID
go

or just remove the default and
INSERT Test (UserID, PermissionsUpdated) SELECT UserID, 0 FROM Inserted

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mparter
Yak Posting Veteran

86 Posts

Posted - 2002-09-04 : 11:49:20

Thanks very much for your help nr, works a treat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources