Constraint Triggers - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Constraint Triggers

Author

Topic

Quish
Starting Member

8 Posts

Posted - 2008-05-01 : 10:12:41

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

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-01 : 10:41:45

This should get you started.


Create Trigger Tenant_room_check On Tenant
Instead Of Insert, Update
Begin
	Declare @Tenant_Count Int
	Declare @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
		--ToDo:
		--Do your insert/update using the Inserted table for the values
	End
End

Quish
Starting Member

8 Posts

Posted - 2008-05-01 : 11:51:23

what is 'Inner Join Inserted I On T.TenantID = I.TenantID
And T.House_ID = I.House_ID'
and
Inner Join Inserted I On R.House_ID = I.House_ID

would the below not work?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-01 : 12:58:34

quote:
Originally posted by Quish

what is 'Inner Join Inserted I On T.TenantID = I.TenantID
And T.House_ID = I.House_ID'
and
Inner Join Inserted I On R.House_ID = I.House_ID

would the below not work?

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

Inserted is a temporary table used by SQL server which holds the values that you are trying to insert to the Tenant table. You need to include this table to get Tenant_ID and House_ID of record you are going to insert to get the count of distinct tenants who are already in the house.

Quish
Starting Member

8 Posts

Posted - 2008-05-02 : 05:23:43

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
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 ??

thanks

Quish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 05:25:21

quote:

Originally posted by Quish

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
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 ??

thanks

Quish

This trigger will operate before actual insert but the inserted table will be populated by values to be inserted while this trigger fires.

Quish
Starting Member

8 Posts

Posted - 2008-05-02 : 06:05:42

the trigger is not actually inserting any data.

if the

Tenant_count > Bedroom_count is true it returns the error
if its false no data is inserted

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-02 : 06:19:08

If you are writing INSTEAD OF trigger, it is your responsibility to add code to insert data to appropriate table. That's the case your data is not getting inserted since you didn't make any provision yourself to insert data to table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 06:20:08

quote:
Originally posted by Quish

the trigger is not actually inserting any data.

if the

Tenant_count > Bedroom_count is true it returns the error
if its false no data is inserted

It wont. thats INSTEAD OF trigger. As the name suggests it will perform the written logic instead of insert action whenever one is triggered on its base table. If you want the insert to happen you need to write the code for it inside trigger.
Insert into table
Select ...
from .....

Quish
Starting Member

8 Posts

Posted - 2008-05-02 : 07:26:21

i think i have cracked it


alter 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('You cannot add more then Bedroom_Count', 10, 1)
	end
	

end

i just need to run it in my application now

thanks guys - if i get stuck again i'll be back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources