Triggers - Comparing Column Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Triggers - Comparing Column Values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-25 : 07:40:12

Chris writes "Hi

I new to SQL Server so sorry if this is an easy question. I am trying to compare values in two columns (Cus_Max_Credit and Cus_Credit_Used) in the same table when an update/insertion is made. I know the error is in the IF statement but I don't know how it should be written. Any help appreciated.

CREATE TRIGGER CREDIT_CHECK ON [dbo].[Customers]
FOR INSERT, UPDATE
AS
IF UPDATE (Cus_Max_Credit) OR UPDATE (Cus_Credit_Used)
BEGIN
IF Cus_Max_Credit < Cus_Credit_Used
RAISERROR ('Maximum credit should always be greater than credit used', 16, 1)
ROLLBACK TRANSACTION
END"

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-25 : 14:09:37

[code]
USE Northwind
GO

SELECT 0 AS Cus_Max_Credit, 0 AS Cus_Credit_Used, * INTO Customer FROM Customers
GO

CREATE TRIGGER CREDIT_CHECK ON Customer
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT * FROM inserted WHERE Cus_Max_Credit < Cus_Credit_Used)
BEGIN
RAISERROR ('Maximum credit should always be greater than credit used', 16, 1)
ROLLBACK TRANSACTION
END
END
GO

INSERT INTO Customer(Cus_Max_Credit, Cus_Credit_Used, CustomerId, CompanyName)
SELECT 0, 10, 1, 'x002548'
GO

DROP TABLE Customer
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources