How to have a relation between a view & a table please.....

Question

How to have a relation between a view & a table please.....

asita

SSCarpal Tunnel

Points: 4144
More actions
April 23, 2010 at 2:42 pm

#219605

Hello All,
1) it is some thidparty Db, let's name it as MRMDB
2) Sql server db, let's name it as AccountsDB
both 1 and 2 db's are on single sql server
i created a view called department (base on 3 MRMDB tables) in Accounts DB
in Accounts Db i have a table called emp, there is no physicall relation between this emp and view department
now how can i make a condition between the table and View
so that when ever user inserts data in emp table it needs to check that dept# should exist in department table
is it possible in db to have relationship between view & table
or any other alternative ways
please sir
if not understood please let me know
i will give you indetail
Thanks in advance
dhani

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

You can't set a constraint between a table and a view, but you could set a trigger that would check to see if the inserted data was in the view.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Thanks Steve,

Could you please Give me one simple exam how to enforce through a trigger

let say for ex: Dept View (Dept#, Name)

and table is Emp (empid, Name, Dept#)

now how can i write a trigget to see that when ever they insert data to emp (dept# should exist in dept# view)

please help me

Thanks In advance

asita

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Something like this:

CREATE TRIGGER emptrigger ON emp for INSERT

AS

BEGIN

IF NOT exists( SELECT e.empid

FROM emp e

INNER JOIN INSERTED i

ON e.empid = i.empid

)

ROLLBACK

END

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Thanks Steve thats wonderfull i will test it

i greatfull to you

Thanks a ton

asita

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

My personal opinion is that this is all a huge design mistake. You shouldn't be using triggers within the same database to enforce referential integrity. That's what Primary and Foreign keys are for.

My recommendation is to look at the underlying tables in the Department view and figure out the right way to do it. If even the underlying tables won't support what you want to do, then you need to throw a rock at the designers and show them the costly mistake they've made that will really hurt things in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)