Re: using a trigger to enforce referential integrity

From: Scott Morris (bogus_at_bogus.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 08:51:10 -0400

The first thing to do is COMPLETELY and EXACTLY specify the error message
you are receiving. Lets avoid "something like..." to avoid confusion and
red herrings. As Hugo and I suggested, the problem is probably related to
WHO is doing the insert. Most likely your tests using EM are done using
adminstrator-level privileges. The same for QA. If you login to QA with
the same permissions as a "real" user of your application, you should be
able to insert a row into the table and see a more complete picture of the
errors and messages generated by the trigger.

Once you've done that, you can then move on to rewriting your trigger to
make it much more efficient and avoid the problem altogether. There is
absolutely NO reason to create "temporary" permanent tables and load them
with the contents of the inserted and deleted tables - and then delete these
same tables at the end of the trigger.

Replace statements that use your "temporary" permanent tables with the
appropriate virtual tables

UPDATE tblItemRelationship
SET ChildItemID = I.ItemID
FROM tblItemRelationship AS IR
JOIN mdsystmpDeleted AS D ON IR.ChildItemID = D.ItemID
JOIN mdsystmpInserted AS I ON D.ItemRecordID = I.ItemRecordID

becomes

UPDATE tblItemRelationship
SET ChildItemID = I.ItemID
FROM deleted AS D
JOIN inserted AS I ON D.ItemRecordID = I.ItemRecordID
WHERE tblItemRelationship.ChildItemID = D.ItemID

IMHO, you should also re-evaluate the process that decided the modification
of ID values was appropriate and allowed. IDs are usually pseudo-keys and
have no meaning.

"pmcguire" <pmcguire@discussions.microsoft.com> wrote in message
news:241799EA-4ED0-4807-8748-7EAB04746942@microsoft.com...
> Hi Hugo,
>
> Thanks for the response. I guess I should also say that I'm not
experiencing this problem in, say, EnterpriseManager, but rather in a VB.NET
app that I've written. The specific error I'm getting might be somehow
misleading, but it SEEMS to indicate that mdsystmpDeleted doesn't exist (the
SQLError message is something like "invalid table name 'mdsystmpDeleted'.").
However I CAN succesfully create a table (regardless of WHO I'm logged in
as) by placing a line in the trigger before the SELECT that creates
mdsystmpDeleted that says : SELECT ItemID INTO tmpTest FROM tblItem (ItemID
is a valid column in tblItem, a valid table in the database); so it doesn't
SEEM to be a problem of not having create privileges.
>
> Thanks
>
> --
> Pat
>
>
> "Hugo Kornelis" wrote:
>
> > On Tue, 27 Jul 2004 14:30:02 -0700, pmcguire wrote:
> >
> > > SELECT ItemID,ItemRecordID INTO mdsystmpDeleted FROM deleted
> >
> > Hi Pat,
> >
> > Though you didn't provide the error message, I bet the above line is
where
> > it's raised. And the error is not caused by referencing the deleted
> > pseudotable, but by attempting to create the mdsystmpDeleted table.
System
> > Administrator has create table priviledge by default, mere mortals
don't.
> >
> > Best, Hugo
> > --
> >
> > (Remove _NO_ and _SPAM_ to get my e-mail address)
> >

Relevant Pages

  • Re: Domain issue
    ... Do you have the Registry Filter component in the image? ... After thirty days,It just display the following error message ... " The system cannot log on now because the domain MYDOMAIN is not ... I want to avoid this type of domain entry ...
    (microsoft.public.windowsxp.embedded)
  • Re: NEED TO CALL AS400 S.P
    ... That is to use a trigger on a table and perform an INSERT/UPDATE which will execute the trigger. ... What an error message! ... > what your OLE DB provider is feed the AS400 with? ... > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se ...
    (microsoft.public.data.oledb)
  • Re: How to find uploaded data size from content-length
    ... so your question actually is: how do I avoid the uploading of big ... If set to a non-negative integer, this variable puts a ceiling on ... it will immediately exit with an error message. ...
    (comp.lang.perl.misc)
  • Re: Write conflict error after adding trigger to table
    ... This error message is usually the result of a missing primary key or a ... VIEW_METADATA and create an appropriate InsteadOf trigger to have this view ... > view in that database. ... > of using the stored proc as a datasource. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Killed by Triggers
    ... When I removed the trigger the error disappeared. ... The second situation is when a detail record was attempted, after the header ... If you don't get an error message, ... UPDATE TABLE01 SET CREATEDBY = CURRENT_USER, ...
    (microsoft.public.sqlserver.security)
Loading