Constraint Triggers

[Quish]

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

[ck9663]

I'm not sure if you can pass a variable into a trigger.

Every time and insert or update is done on a table, it will create a logical table called inserted and for update statement another table called deleted. You can get the values from there.

Read more here

-- CK

[Quish]

A friend gave me some pointers and i have come up with this

Create Trigger Tenant_room_check On Tenant
Instead Of Insert, Update
Begin

   Declare 
    @Tenant_Count Int
   ,@Bedroom_Count Int

   Select @Tenant_Count = Count(Distinct T.Tenant_ID)
   From Tenant T
   Inner Join Inserted I On T.TenantID = I.TenantID
                   And T.House_ID = I.House_ID
   Where Tenant_status = 1

   Select @Bedroom_Count = Count(Distinct R.ROOM_ID)
   From Room R
   Inner Join Inserted I On R.House_ID = I.House_ID

   If @Tenant_Count > @Bedroom_Count
   Begin
                   RaisError('you cannot have more tenants than the number of bedrooms in a student house', 10, -20601)
   End
   Else Begin

	exec insertTenant

   End

End

but now i am getting this error

Server: Msg 156, Level 15, State 1, Procedure Tenant_room_che ck, Line 5
Incorrect syntax near the keyword 'Begin'.

Any ideas

Many Thanks

Quish

[Quish]

i missed keyword "AS" between "Instead of Insert, Update" and "Begin".

[Quish]

Even with the amendment of AS i am still getting problems.
after some thinking regarding what i actually wanted the trigger to do i created this based on the previous examples.

ALTER Trigger Tenant_room_check On Tenant
Instead Of Insert, Update
AS
Begin

   Declare 
    @Tenant_Count Int
   ,@Bedroom_Count Int

   Select @Tenant_Count = Count(Distinct T.Tenant_ID)
   From Tenant T
   where  T.House_ID = 1
   and Tenant_status = 1

   Select @Bedroom_Count = Count(Distinct R.ROOM_ID)
   From Room R
   where R.House_ID = 1
   and room_bedroom = 1


   If @Tenant_Count = @Bedroom_Count
  
                   RaisError('you cannot have more tenants than the number of bedrooms in a student house', 10, 1)

   END

but I struggling to understand at what point data would be inserted. does this trigger operate on insert or before ??

Qiush

[Quish]

got there in the end

thanks you all your help


create Trigger Tenant_room_che ck On Tenant
Instead Of Insert
AS
Begin

Declare
@Tenant_Count Int
,@Bedroom_Count Int
,@HOUSE_ID INT

Select @Tenant_Count = Count(Distinct T.Tenant_ID)
From Tenant T
where T.House_ID = @House_ID
and Tenant_status = 1

Select @Bedroom_Count = Count(Distinct R.ROOM_ID)
From Room R
where R.House_ID = @House_ID
and room_bedroom = 1

If @Tenant_Count < @Bedroom_Count
begin
RaisError('TENA NT ADDED', 10, 1)

---INSERT STATEMENT HERE ---

end
IF @Tenant_Count >= @Bedroom_Count
begin
RaisError('not added', 10, 1)
end


end