May 8, 2008 at 10:43 am
I have a table (action) which contains a nullable column (creatorid) whose value, if provided, must be the primary key (id) of a row in another table (user) that has an 'Inactive' value of 0. Using a trigger with the following test ....
IF NOT EXISTS (SELECT 1 FROM Inserted AS i JOIN dbo.user AS u ON i.creatorid = u.id WHERE u.Inactive = 0)
... I can determine if a supplied value is valid.
My question is "How do I determine if the creatorid value was omitted or supplied with a value of NULL in an INSERT statement?", which would result in the NOT EXISTS condition being true, but not an error.
An additional problem is that I also have another column (originatorid) in the action table which must also be an active user.
May 8, 2008 at 12:37 pm
I think this is what you are trying to do:
[font="Courier New"]SELECT
*
FROM
Inserted I
LEFT JOIN dbo.User U ON I.CreatorID = U.ID AND U.Inactive = 0
WHERE
I.CreatorID IS NOT NULL
AND U.ID IS NULL[/font]
I would tend to not want to do this type of referential integrity in a trigger. This is a case in which you may want to consider a check constraint or putting the logic into the program or stored procedure actually doing the inserting.
May 8, 2008 at 12:58 pm
Figured it out ....
IF EXISTS (SELECT creatorid FROM Inserted WHERE creatorid IS NOT NULL)
BEGIN
IF NOT EXISTS (SELECT 1 FROM Inserted AS i JOIN dbo.Users AS u ON i.creatorid = u.userid
WHERE u.Inactive = 0)
BEGIN
RAISERROR('Invalid or inactive creatorid specified', ......
.
.
.
END
May 8, 2008 at 1:03 pm
That will only work if you cannot insert more than one row at a time. Triggers in SQL are set-based - your INSERTED table may have one row with a NULL value and you will not check any of the other rows.
May 8, 2008 at 1:10 pm
C D Brinkerhoff (5/8/2008)
Figured it out ....IF EXISTS (SELECT creatorid FROM Inserted WHERE creatorid IS NOT NULL)
BEGIN
IF NOT EXISTS (SELECT 1 FROM Inserted AS i JOIN dbo.Users AS u ON i.creatorid = u.userid
WHERE u.Inactive = 0)
BEGIN
RAISERROR('Invalid or inactive creatorid specified', ......
.
.
.
END
that logic will not catch a batch which includes both valid and invalid creatorids. m.earl's earlier suggestion to use a outer join is better.
if exists (
select 1
from inserted I left join dbo.Users U on I.creatorId = U.userId
where U.creatorId is null -- invalid creatorId
or U.Inactive ! = 0 -- inactive user
)
begin
...
even if your app only inserts/updtes 1 row at a time, the trigger should be robust enough to handle ad-hoc data manipulations (which are often the root cause of many bugs).
May 10, 2008 at 10:53 pm
This is a single insert only situation so works fine, thx for the replies
May 12, 2008 at 6:04 am
It is still worth handling multiple rows. You have the necessary code. If you don't do it now, when you have a process that inserts multiple records you will be pulling your hair out wondering why you are getting errors. It is easy to forget about a trigger.
November 3, 2011 at 4:52 pm
Is it possible to set a foreign key to the pkey of same table and turn cascaded delete on? The data I have is like a tree structure, and I want to give the option that on deleting a node, all the child nodes will also be deleted.
Table: treeStruct{node, foreign_node,data}
pkey: node
fkey: parent_node
all the nodes are stored in same table. E.g. There is following data in table:
node| parent_node| data
0 | null | root
1 | 0 |level0item0
2 | 0 |level0item1
3 | 1 |level1item0
Thus doing a delete where node = 0 should effectively delete all data in this table
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply