Inserting Records in a View with Join by a trigger

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)

Inserting Records in a View with Join by a trigger

Author

Topic

PersianH
Starting Member

3 Posts

Posted - 2002-02-26 : 09:33:21

Dear Friends

Please Suppose this senario:
You have created the following tables and view:

CREATE TABLE Users (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[UName] [char] (10) NULL ,
[Pass] [char] (10) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED([Code])
)

CREATE TABLE UsersTel (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[UCode] [int] NOT NULL ,
[TelNum] [char] (12),
CONSTRAINT [PK_UsersTel] PRIMARY KEY CLUSTERED ([Code]),
CONSTRAINT [FK_UsersTel_Users] FOREIGN KEY ([UCode]) REFERENCES [Users] ([Code]) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE VIEW UsersView (UName, Pass, TelNum) AS
SELECT Users.UName, Users.Pass, UsersTel.TelNum FROM
Users INNER JOIN UsersTel ON Users.Code = UsersTel.UCode

Now, I would be appreciative if you could give me some advise on wrting an INSETEAD OF INSERT Trigger on UsersView for adding a new record to it.
What I really don't know is how can I establish the relation between two new records in the trigger.

Thanks in advanced

Regards
Persian Handsome Man

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-26 : 09:53:54

create trigger x on UsersView instead of update
as
update UsersTel
set TelNum = inserted.TelNum
from users, inserted
where Users.UName = inserted.UName
and users.Code = userstel.ucode

Can do the same for the other table.
Should return UsersTel.Code in the trigger so that you can map to the tables correctly from the inserted and deleted tables. I have used UName in the above which will of course not work if it is not unique or being updated.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

PersianH
Starting Member

3 Posts

Posted - 2002-02-27 : 06:06:14

Dear nr,

Thanks for your reply and the solution, but unfortunatley it doesn't work. Maybe I have not described the problem correctly.
As you may noticed, the Code column is an IDENTITY. Inserting the data in the second table, UsersTel, needs the new generated value from Users.Code for UCode column and TelNum from the inserted table.
Consider the following TRIGER:

CREATE TRIGGER UT ON UsersView INSTEAD OF INSERT AS
INSERT INTO Users (UName, Pass) SELECT UName, Pass From inserted
--INSERT INTO UsersTel (UCode, TelNum) <<Here we need something>>

What I think we need is a confident way to determine the generated value for Users.Code

I'm appreciting your kind and help.

Persian Handsome Man

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-27 : 10:02:14

create trigger xins on UsersView instead of insert
as
insert Users select inserted.UName, inserted.Pass from inserted
insert UsersTel select @@identity, inserted.TelNum from inserted
go

Will need error processing.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

PersianH
Starting Member

3 Posts

Posted - 2002-02-28 : 15:22:52

Thanks dear nr,

That was what I had in mind. You were a big help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources