January 27, 2006 at 3:49 am
I have to enforce business rules that say certain values can only be used with certain other values. So I create a Permissable Combination table, which lists all the valid combinations. Then when an insert or update is made, the trigger checks the combination table to see if it is valid and throws an exception if it is not.
THE PROBLEM IS: Our db has ANSI_NULLS off, so if one of the values is null and null is a valid combination in some cases, then my trigger fails.
Here's the trigger:
CREATE TRIGGER trigIU_ORG_TYPE
ON ORG_TYPE
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @blab blah ....
/* see how many rows are being inserted */
select @numinserted = count(*) from inserted
/* see how many inserted rows match the valid combinations */
SELECT @numrows = count(*) FROM inserted i inner join
DOM_ORG_TYPE_PC p on i.function_code = p.function_code
AND i.cmd_code = p.cmd_code
/* if any of the rows fail, kill the transaction */
if (@numinserted <> @numrows )
BEGIN
SELECT @errno = 30012,
@errmsg = 'Cannot insert/update ORG_TYPE: combination invalid'
GOTO error
END
RETURN
error:
RAISERROR @errno @errmsg
ROLLBACK TRANSACTION
END
The DOM_ORG_TYPE_PC table tooks like this:
PC_id function_code cmd_code
----------- ------------- --------
1 YES CP
2 YES CC
3 YES HQ
4 NO NULL
The ORG_TYPE table looks like:
CREATE TABLE [ORG_TYPE] (
...PK goes here...
[function_code] char(3) NOT NULL ,
[cmd_code] char(6) NULL
) ON [PRIMARY]
So if I run the following:
insert into org_type values ( 'YES', 'CC')
insert into org_type values ( 'YES', 'HQ')
Everything runs as I expect, codes are valid. But try ...
insert into org_type values ('NO', null)
and the trigger throws the exception. I've tried ANSI_NULLS both off and on, but it doesn't seem to matter.
Can anyone see what I'm doing wrong here??
if I have to select the cmd_code from inserted to see if it is null, and then put "where cmd_code is null" in my join, then it will mess up if there are multiple row inserts. I'm alittle confused about how to solve this.
Thanks for any advice. - Muaddib
January 27, 2006 at 12:04 pm
suggestion - why not use your pc_id in your org_type table instead of function_code and cmd_code ?!
**ASCII stupid question, get a stupid ANSI !!!**
January 27, 2006 at 12:20 pm
Killing 2 birds with the 1 stone, first off the NULL issue, then the looming performance issue by using inefficient and unnecessary counts:
IF EXISTS (
SELECT *
FROM inserted as i
WHERE NOT EXISTS (
SELECT *
FROM DOM_ORG_TYPE_PC As p
WHERE (p.function_code = i.function_code Or
(p.function_code Is Null And i.function_code Is Null))
AND (i.cmd_code = p.cmd_code Or
(i.cmd_code Is Null And p.cmd_code Is Null))
)
)
BEGIN
SELECT @errno = 30012,
@errmsg = 'Cannot insert/update ORG_TYPE: combination invalid'
GOTO error
January 31, 2006 at 4:21 am
Thanks PW,
That will work fine. I didn't think about the OR and checking for NULL on both tables. Of course it looks simple now that I see it. Also, I thought the count(*) would be fast enough since count uses index, but your exist solution is much better.
Sushila, I can't use the PC_id like it is a normal db, I'm working on an international defense project and some of the tables are not mine to change. I have to add tables and relationships to validate their data, but I can't change schemas.
- Muaddib
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply