Create validity checking trigger

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 2005 Forums

Transact-SQL (2005)

Create validity checking trigger

Author

Topic

mojam
Starting Member

5 Posts

Posted - 2009-09-27 : 05:35:40

I have Created three tables
1. Product
i. productid int primary key,
ii.productname varchar(50),
iii.model varchar(20),
iv.desc varchar(100)

2. Purchase
i. purchaseid int primary key,
ii. productid int foreign key,
iii.qty int,
iv.rate double,
v.total double

3. Sale
i. saleid int primary key,
ii. productid int foreign key,
iii.qty int,
iv.rate double,
v. total double

-- when I shall entry data into sale table, I have to ensure that sale qty would not greater than sum of the qty of purchase of any specific product i.e- sale.qty<=sum(purchase.qty)

Here I want to use Use Trigger to Validate the Condition in the Sales table, but I don't know how to create it and use it.

Can anybody kindly help me by create a trigger above criteria pls help me

smarty
Starting Member

13 Posts

Posted - 2009-09-28 : 03:15:46

Here's an example:
CREATE TRIGGER [YOUR].[trgCheckID] ON [YOUR].[TABLE]
FOR INSERT, UPDATE AS

DECLARE @ID AS INT

SELECT @ID = ID FROM INSERTED

IF @ID IS NOT NULL
BEGIN
IF NOT EXISTS (SELECT ID FROM [YOUR].[OTHERTABLE] WHERE ID = @ID)
RAISERROR ('Trigger Error : The ID provided does not exist in [YOUR].[OTHERTABLE]', 12, 1) WITH LOG
END

GO

-----------------------------------
Free SQL server monitoring for DBA's
www.realsmartsoftware.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources