By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 1,470 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

Constraint Triggers

P: 22
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE TRIGGER Tenant_room_check
  3. BEFORE INSERT or update of tenant_ID ON Tenant 
  4. FOR each row
  5. as (new.tenant_ID  is not null)
  6. DECLARE 
  7.  
  8. Tenant_count NUMBER;
  9. Bedroom_count NUMBER;
  10.  
  11. BEGIN
  12.  
  13. select count(Tenant_ID) as Tenant_count
  14. from Tenant 
  15. where tenant_ID = :new.tenant_ID
  16. and House_ID = 1
  17. AND Tenant_status = 1;
  18.  
  19. select count(ROOM_ID) as bedroom_count
  20. from Room 
  21. where Room_Name = 'Bedroom'
  22. and House_ID = 1;
  23.  
  24.         if (Tenant_count > Bedroom_count)
  25.         then raise_application_error(-20601,
  26.         'you cannot have more tenants than the number of bedrooms in a student house');
  27.  
  28. END IF;
  29. END;          
  30. /
  31.  
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
May 1 '08 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
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
May 1 '08 #2

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Create Trigger Tenant_room_check On Tenant
  3. Instead Of Insert, Update
  4. Begin
  5.  
  6.    Declare 
  7.     @Tenant_Count Int
  8.    ,@Bedroom_Count Int
  9.  
  10.    Select @Tenant_Count = Count(Distinct T.Tenant_ID)
  11.    From Tenant T
  12.    Inner Join Inserted I On T.TenantID = I.TenantID
  13.                    And T.House_ID = I.House_ID
  14.    Where Tenant_status = 1
  15.  
  16.    Select @Bedroom_Count = Count(Distinct R.ROOM_ID)
  17.    From Room R
  18.    Inner Join Inserted I On R.House_ID = I.House_ID
  19.  
  20.    If @Tenant_Count > @Bedroom_Count
  21.    Begin
  22.                    RaisError('you cannot have more tenants than the number of bedrooms in a student house', 10, -20601)
  23.    End
  24.    Else Begin
  25.  
  26.     exec insertTenant
  27.  
  28.    End
  29.  
  30. End
  31.  
  32.  
but now i am getting this error

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

Any ideas

Many Thanks

Quish
May 1 '08 #3

P: 22
i missed keyword "AS" between "Instead of Insert, Update" and "Begin".
May 1 '08 #4

P: 22
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.

Expand|Select|Wrap|Line Numbers
  1. ALTER Trigger Tenant_room_check On Tenant
  2. Instead Of Insert, Update
  3. AS
  4. Begin
  5.  
  6.    Declare 
  7.     @Tenant_Count Int
  8.    ,@Bedroom_Count Int
  9.  
  10.    Select @Tenant_Count = Count(Distinct T.Tenant_ID)
  11.    From Tenant T
  12.    where  T.House_ID = 1
  13.    and Tenant_status = 1
  14.  
  15.    Select @Bedroom_Count = Count(Distinct R.ROOM_ID)
  16.    From Room R
  17.    where R.House_ID = 1
  18.    and room_bedroom = 1
  19.  
  20.  
  21.    If @Tenant_Count = @Bedroom_Count
  22.  
  23.                    RaisError('you cannot have more tenants than the number of bedrooms in a student house', 10, 1)
  24.  
  25.    END
  26.  
but I struggling to understand at what point data would be inserted. does this trigger operate on insert or before ??

Qiush
May 2 '08 #5

P: 22
got there in the end

thanks you all your help


create Trigger Tenant_room_check 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('TENANT ADDED', 10, 1)

---INSERT STATEMENT HERE ---

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


end
May 2 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.