Author |
Topic |
philtonite
Starting Member
6 Posts |
Posted - 2008-04-08 : 10:19:34
|
Hi! I would like to know if someone could help. I'm not sure whether to use a trigger or a procedure for the following: in a BUILDING table it has a TOTALASM (ASM - assignable square metres) field which should be calculated by adding the SQMETRES of each room in the ROOM table which corresponds to a particular room. How should I do this? With a trigger or procedure? |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-04-08 : 10:49:07
|
Trigger on the ROOM table.e4 d5 xd5 Nf6 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-08 : 12:05:48
|
quote: Originally posted by philtonite Hi! I would like to know if someone could help. I'm not sure whether to use a trigger or a procedure for the following: in a BUILDING table it has a TOTALASM (ASM - assignable square metres) field which should be calculated by adding the SQMETRES of each room in the ROOM table which corresponds to a particular room. How should I do this? With a trigger or procedure?
You want this calculation to happen everytime when a new ROOM record is added? then use trigger. |
|
|
mailbalajijagan@gmail.com
Starting Member
2 Posts |
Posted - 2008-04-09 : 03:33:02
|
Create a View by joining the BUILDING and ROOM table and have the calculation done in the view. This view will be getting updated for the corresponding DML operations |
|
|
philtonite
Starting Member
6 Posts |
Posted - 2008-04-09 : 06:53:21
|
Thanx!!! I'll give it a go when I get back to work! |
|
|
philtonite
Starting Member
6 Posts |
Posted - 2008-04-14 : 10:54:05
|
thanx guys that worked! i got another question if you guys can help? i'm buidling a front-end for db on mssql 2000. i have made a jdbc odbc connector to my server but when i want to select tables it says that none of them have primery keys but they all do. even in netbeans database explorer it shows that the tables have primary keys! any ideas? |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-04-14 : 12:28:41
|
Script out the DDL for one of the tables it claims has not primary keys, and post it here.e4 d5 xd5 Nf6 |
|
|
philtonite
Starting Member
6 Posts |
Posted - 2008-04-15 : 04:46:47
|
thanx for your help. i think this is what you want:/****** Object: Table [dbo].[CAMPUS] Script Date: 15/04/2008 10:45:02 ******/CREATE TABLE [dbo].[CAMPUS] ( [CAMPCODE] [varchar] (3) COLLATE Latin1_General_CI_AS NOT NULL , [CAMPNAME] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL , [CAMPPSTC] [varchar] (4) COLLATE Latin1_General_CI_AS NULL , [CAMPPROV] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL , [CAMPPREFIX] [varchar] (10) COLLATE Latin1_General_CI_AS NULL , [dude] [uniqueidentifier] NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[CAMPUS] WITH NOCHECK ADD CONSTRAINT [PK_CAMPUS] PRIMARY KEY CLUSTERED ( [CAMPCODE] ) ON [PRIMARY] GOALTER TABLE [dbo].[CAMPUS] ADD CONSTRAINT [FK_CAMPUS_CAMPPROVCODES] FOREIGN KEY ( [CAMPPROV] ) REFERENCES [dbo].[CAMPPROVCODES] ( [CAMPPROV] )GO |
|
|
philtonite
Starting Member
6 Posts |
Posted - 2008-04-15 : 05:50:33
|
i even tried the example databases that come with ms sql 2000 and netbeans also does not see their primary keys! i've tried also instead to create unique not null fields as the primary keys but with no success! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-04-15 : 08:57:14
|
Sure looks like your table has a primary key to me.So the problem is not with your database, but with net beans.See if there is a net bean forum where people could point you in the right direction.e4 d5 xd5 Nf6 |
|
|
|