SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Trigger or procedure?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

philtonite
Starting Member

South Africa
6 Posts

Posted - 04/08/2008 :  10:19:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/08/2008 :  10:49:07  Show Profile  Reply with Quote
Trigger on the ROOM table.

e4 d5 xd5 Nf6
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/08/2008 :  12:05:48  Show Profile  Reply with Quote
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.
Go to Top of Page

mailbalajijagan@gmail.com
Starting Member

2 Posts

Posted - 04/09/2008 :  03:33:02  Show Profile  Reply with Quote
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
Go to Top of Page

philtonite
Starting Member

South Africa
6 Posts

Posted - 04/09/2008 :  06:53:21  Show Profile  Reply with Quote
Thanx!!! I'll give it a go when I get back to work!
Go to Top of Page

philtonite
Starting Member

South Africa
6 Posts

Posted - 04/14/2008 :  10:54:05  Show Profile  Reply with Quote
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?
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/14/2008 :  12:28:41  Show Profile  Reply with Quote
Script out the DDL for one of the tables it claims has not primary keys, and post it here.

e4 d5 xd5 Nf6
Go to Top of Page

philtonite
Starting Member

South Africa
6 Posts

Posted - 04/15/2008 :  04:46:47  Show Profile  Reply with Quote
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]
GO

ALTER TABLE [dbo].[CAMPUS] WITH NOCHECK ADD
CONSTRAINT [PK_CAMPUS] PRIMARY KEY CLUSTERED
(
[CAMPCODE]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CAMPUS] ADD
CONSTRAINT [FK_CAMPUS_CAMPPROVCODES] FOREIGN KEY
(
[CAMPPROV]
) REFERENCES [dbo].[CAMPPROVCODES] (
[CAMPPROV]
)
GO

Go to Top of Page

philtonite
Starting Member

South Africa
6 Posts

Posted - 04/15/2008 :  05:50:33  Show Profile  Reply with Quote
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!
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/15/2008 :  08:57:14  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000