" If condition" as one of the constraints to a Column Field ?

Question

I have a Sales tax column in one of my SQL Table where Data is stored.

I was wondering is it possible to place an constraint on the table itself for the Sales Tax

Column saying " if > June 2008 tax is 2 % " else "tax is 4 %"?

Should that come from stored procs related to that Table?

Answer 1

Do you want the tax to be auto-populated?

Constraints only perform verification, not population of data, which can be done by stored procs or triggers.

Answer 2

You probably need to use a trigger rather than a constraint. An AFTER INSERT trigger should do the trick.

Answer 3

If you want to make sure that the column 'tax' is 2 or 4 depending on the month (e.g., month 9 = September), then you could do this:

ALTER TABLE SomeTable 
   ADD CONSTRAINT CK_SalesTax
   CHECK ((MONTH(GETDATE()) = 9 AND SalesTax = 2) OR (MONTH(GETDATE()) != 9 AND SalesTax = 4))

Obviously, vary for your conditions. e.g., to test for dates after June 2008, it's a bit simpler.

(GETDATE() >= '1 June 2008' AND SalesTax = 2)

You should be able to build this into a CHECK constraint of using a similar mechanism as I've dropped in the first example.

Note this only checks the value that put in to the table. It won't auto-populate. As other people have noted, if you want auto-population, you need a trigger.

Answer 4

You could use a constraint to achieve this effect...

pseudo-SQL...

(YourDate >= '6/1/08' and YourTaxData = 0.02) or (YourTaxData = 0.04)

You might consider instead using a table to host the tax values and using your queries to pull the appropriate tax value for a given date/location. That's more scalable than the constraint.

Answer 5

here is a simple way you can alter or create a new table

 create table #test (
Date datetime,
amount money,
tax as case when date > '06/01/08' then convert(decimal(10,2),4.00) else convert(decimal(10,2),2.00) end,
)

insert into #test
select '05/01/08', 10.00

insert into #test
select '07/01/08', 10.00

select * from #test