Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 31st, 2006, 02:31 AM
Friend of Wrox
 
Join Date: Feb 2006
Location: noida, UP, India.
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default Writing Rule Problem

Hello Friends,
Can you tell me if I can make a rule as follow or not :
CREATE RULE rul_name AS
@date1 <= GETDATE();
Will it work???

Gaurav
__________________
Gaurav
  #2 (permalink)  
Old July 31st, 2006, 05:40 AM
Registered User
 
Join Date: Jul 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes. You can
See below:

CREATE RULE rul_name AS
@date1 <= 10
go
CREATE TABLE [t2] (c1 int)
go
sp_bindrule rul_name, '[t2].c1'
go
INSERT INTO [t2] select 10
INSERT INTO [t2] select 12


  #3 (permalink)  
Old July 31st, 2006, 05:45 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

It will work.

Note that the CREATE RULE statement is deprecated, and CHECK CONSTRAINT should be used instead.

I did find that while trying to execute the CREATE RULE statement in QA, that it did not like the trailing ";", despite the fact that BOL indicates that the terminator is optional. Strange.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #4 (permalink)  
Old August 2nd, 2006, 02:11 AM
Friend of Wrox
 
Join Date: Feb 2006
Location: noida, UP, India.
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default

But the thing that I want to do cannot be checked by a CHECK constraint. I want to ckeck whether the entered date in a table is <= today's date. I have two options : RULE and TRIGGER. Now can you tell me which one is better to use(Which is faster and which one should prefer to use).

Gaurav
  #5 (permalink)  
Old August 2nd, 2006, 05:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Why do you think you cannot construct a CHECK CONSTRAINT on a table column using GetDate()?
Code:
ALTER TABLE yourtable ADD CONSTRAINT CK_col CHECK (yourcol <= GetDate())
will create such a constraint.

I doubt there is any performance difference between a RULE and a CONSTRAINT. There is nothing preventing you from using a RULE, it's just that the CONSTRAINT syntax is SQL Standard and the RULE is not.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #6 (permalink)  
Old August 3rd, 2006, 12:51 AM
Friend of Wrox
 
Join Date: Feb 2006
Location: noida, UP, India.
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default

Thank you again. I will use CHECK for it.
Please tell me that among trigger and rule, which is faster and which is better performance-wise.

Gaurav Jain.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with a validation rule Hliu Access VBA 1 September 3rd, 2008 09:42 AM
problem writing in ASP naureen tahir ASP.NET 2.0 Basics 2 May 23rd, 2007 05:11 AM
Problem in writing within "" by DOM. Somesh XSLT 1 March 5th, 2007 07:27 AM
Create a validation rule sheregardner Access 1 October 24th, 2006 07:00 AM
CSS2: Horizontal Rule Has 1px Border in IE??? kwilliams CSS Cascading Style Sheets 4 April 27th, 2006 09:53 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.