MSSQL Trigger Help

[Kelv]

I have a table with two integers representing rooms and buildings. I want to create a trigger for on insert and update to make check if there are 100 rooms in the building. If 100 already exist, I want to rollback the update. I think I can figure the condition out but I'm not sure about the syntax.

What do I write if I want to check if the building number of this new tuple is equal to 3. if it is equal, rollback, else allow the insert.



CREATE TRIGGER dbo.AddRoom
ON dbo.RoomAndBuilding
AFTER INSERT, UPDATE
AS
BEGIN

Condition and Actions

END
GO

Can someone help me out or guide me to a good resource. Thanks.

 

[PTNL]

A trigger would take place after the INSERT/UPDATE transaction, and would not be able to execute a ROLLBACK. And given your other requirement for checking other details of the tuple, you're better off using a stored proc.

 

[mikeblas]

SQL Server's own online references are a great resource. You'll want to look at "INSTEAD OF" triggers, where you can test values before they hit the table. The example in the trigger topic gives you pretty much everything you need.

You can use triggers to avoid a particular value in the insert or update as well. You might use a constraint, instead.