Author |
Topic |
Quish
Starting Member
8 Posts |
Posted - 2008-05-01 : 10:12:41
|
Hello allThe 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 @variablenameI have been looking over forums and in books but i am getting all confused over the syntax to use.Please HelpMany ThanksQuish |
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-05-01 : 10:41:45
|
This should get you started.Create Trigger Tenant_room_check On TenantInstead Of Insert, UpdateBegin 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 EndEnd |
|
|
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_IDwould 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_IDwould 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 TenantInstead Of InsertASBegin 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 TenantInstead Of InsertASBegin 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 AthalyeIndia."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 tableSelect ...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 TenantInstead Of InsertASBegin 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 nowthanks guys - if i get stuck again i'll be back |
|
|
|