Check Constraint ot Instead of trigger ?

Hi All.. I have a vendor supplied application that writes to a very large
table. I have identified a set of data that the vendors client software send
s
the database that we do not use, but adds 1 million rows of data each day.
I've added an "After" trigger to delete this data as it gets inserted, but
I'm searching for a way from keeping it from getting inserted in the first
place. I can create a check constraint or an instead of trigger. The docs
say use contraints when you can, but does anyone have a different opinion as
how to redirect data to the bit bucket and not insert it into the table ?
And, no the vendor won't help and I can't say who it is ? :>Is the Vendor insert coming from a Strored Procedure? If so, implement your
logic inside the stored procedure before even doing anything to the table
itself.
"JohnW" wrote:

> Hi All.. I have a vendor supplied application that writes to a very large
> table. I have identified a set of data that the vendors client software se
nds
> the database that we do not use, but adds 1 million rows of data each day.
> I've added an "After" trigger to delete this data as it gets inserted, but
> I'm searching for a way from keeping it from getting inserted in the first
> place. I can create a check constraint or an instead of trigger. The docs
> say use contraints when you can, but does anyone have a different opinion
as
> how to redirect data to the bit bucket and not insert it into the table ?
> And, no the vendor won't help and I can't say who it is ? :>|||On Wed, 26 Apr 2006 14:09:02 -0700, JohnW wrote:

>Hi All.. I have a vendor supplied application that writes to a very large
>table. I have identified a set of data that the vendors client software sen
ds
>the database that we do not use, but adds 1 million rows of data each day.
>I've added an "After" trigger to delete this data as it gets inserted, but
>I'm searching for a way from keeping it from getting inserted in the first
>place. I can create a check constraint or an instead of trigger. The docs
>say use contraints when you can, but does anyone have a different opinion a
s
>how to redirect data to the bit bucket and not insert it into the table ?
>And, no the vendor won't help and I can't say who it is ? :>
Hi John,
Adding a CHECK constraint will prevent the insertion of the data and
throw an error. Also, if 100 rows are inserted in a single INSERT
statement and 1 fails the CHECK constraint, they are all rejected. If
this is the behaviour you need, AND the thrid party application can
handle the error condition raised by the CHECK contraint, use it.
Otherwise, use an INSTEAD OF trigger. That can prevent insertion of all
rows, or of only the offending rows, without sputtering error messages
to the client.
Hugo Kornelis, SQL Server MVP|||Just another solution, though if u can do it in the SP thats the best way..
You can use a for insert trigger and
check if the insert should happen, if not rollback the transaction.
like this
(this not syntax.. just pseudo code) check this anyways
create trigger t1 "for insert" on tbl1
begin
if condition = false
rollback tran
end