Constraint Trigger

Hello all

The majority of my database experience comes from ORACLE and i am trying to use some functionality that i have already used in Oracle into a project i am working on in MSDE.

I want to create a trigger that uses a DML constraint that will prevent a tenants from being inserted into a house if the bedroom count is less or equal to the number of tenants currently in the house.

The oracle code is below


CREATE OR REPLACE TRIGGER Tenant_room_check
BEFORE INSERT or update of tenant_ID ON Tenant
FOR each row
as (new.tenant_ID is not null)
DECLARE
Tenant_count NUMBER;
Bedroom_count NUMBER;
BEGIN

select count(Tenant_ID) as Tenant_count
from Tenant
where tenant_ID = :new.tenant_ID
and House_ID = 1
AND Tenant_status = 1;

select count(ROOM_ID) as bedroom_count
from Room
where Room_Name = 'Bedroom'
and House_ID = 1

if (Tenant_count > Bedroom_count)
then raise_application_error(-20601,
'you cannot have more tenants than the number of bedrooms in a student house');

END IF;
END;
/

Ideally I would like to pass the HOUSE_ID and the TENANT_ID from my application using @variablename

I have been looking over forums and in books but i am getting all confused over the syntax to use.

Please Help
Many Thanks
Quish