Hi all,

I have created a INSTEAD OF INSERT TRIGGER but I seem to have a problem with
NULL values.

The trigger checks to see if a suppliers product code enetred is stockable
and if so gets the Item Description, Price Each based on Quantity and the VAT
Code. All 3 columns do not allow NULL. If the suppliers product code is not
not stockable it uses the users entered values for these 3 columns.

When using Enterprise Manager and I just enter the basic information in to
the table it throws me the error saying that I can't have NULL in those 3
columns.

I have worked round the problem at the moment allowing these 3 columns to
have NULL values and workes fine but how do I get it where I don't allow NULL
values and be able to enter the data in Enterprise Manager?

My trigger reads:

ALTER  TRIGGER InsertPurchaseOrderLineTrigger ON dbo.PurchaseOrderLines
INSTEAD OF INSERT AS
    INSERT INTO PurchaseOrderLines (
                        PurchaseOrderID,
                        SuppliersProductCode,
                        ItemDescription,
                        PriceEach,
                        Quantity,
                        VATCode
                    )
            SELECT
                        i.PurchaseOrderID,
                        i.SuppliersProductCode,
                        CASE
dbo.Stockable(dbo.GetSupplierIDFromPurchaseOrderID(i.PurchaseOrderID),
i.SuppliersProductCode)
                            WHEN 1 THEN
dbo.GetProductNameBySuppliersProductCode(dbo.GetSupplierIDFromPurchaseOrderID(i.PurchaseOrderID), i.SuppliersProductCode)
                            ELSE i.ItemDescription
                        END AS ItemDescription,
                        CASE
dbo.Stockable(dbo.GetSupplierIDFromPurchaseOrderID(i.PurchaseOrderID),
i.SuppliersProductCode)
                            WHEN 1 THEN
dbo.GetSuppliersProductCurrentPrice(dbo.GetSupplierIDFromPurchaseOrderID(i.PurchaseOrderID), i.SuppliersProductCode, i.Quantity)
                            ELSE i.PriceEach
                        END AS PriceEach,
                        i.Quantity,
                        CASE
dbo.Stockable(dbo.GetSupplierIDFromPurchaseOrderID(i.PurchaseOrderID),
i.SuppliersProductCode)
                            WHEN 1 THEN
dbo.GetSuppliersProductCurrentVATCode(dbo.GetSupplierIDFromPurchaseOrderID(i.PurchaseOrderID), i.SuppliersProductCode, i.Quantity)
                            ELSE i.VATCode
                        END AS VATCode
            FROM inserted i

I am using 2 tables here, 1 named PurchaseOrders and the other named
PurchaseOrderLines. I did try and create a DEFAULT on a nvarchar column but
it kept converting it to text instead of calling the function.

Any help would be great thanks.

Paul

It would be nice to see some DDL for the tables and the functions. I hope
you're not planning on using EM to insert data in production.

And could you please explain the purpose of your current logic: you check
inserted values and if the check fails, you allow inserting whatever data was
originally inserted?


ML

I have so many tables and is why I did not publish DDL, I have given enough
information of what I am trying to achive, also not planning to use EM for
entry - just for testing. The solution I have just now workes fine, I would
just like to know if it is possible to do the way I originally wanted to. My
Check Constraints work fine and this is how I prevent NULL at the moment.

Paul

Show quoteHide quote

The only suspect I can see, based on the data given, are the functions you
use to get the data when dbo.Stockable() = 1.

Have you checked whether those return a null value for the test insert values?


ML

Hi,

The Stockable Formula always returns 0 or 1 and again this is not a issue,
the only issue is with the trigger and the 3 columns ItemDescription,
PriceEach and VATCode.

Paul

Show quoteHide quote

Actually I was refering to the other three functions. Is it possible they
return null?


ML

On Fri, 22 Jul 2005 06:47:06 -0700, Dr. Paul Caesar - CoullByte (UK)
Limited wrote:

Show quoteHide quoteHi Paul,

Unfortunately, the NULL constraint is checked before the INSTEAD OF
trigger is fired. This means that you can't omit the nullable columns
from your insert statements, even if the values will be provided by the
trigger.

Instead of omitting the column or explicitly supplying a NULL value, try
supplying a default value. The exact value is irrelevant, since it will
be overriden by your trigger-generated value.

Example:

INSERT INTO MyTable (KeyColumn, ColumnThatWillBeGenerated)
SELECT @KeyValue, 0    -- 0 is a placeholder;
            -- real value will be calculated in trigger

Best, Hugo

The CHECK Constraint is allowing me to do what I want, I am talking about
when using EM it seems to want VALUES before the trigger is executed, the
trigger provides values so I want to be able to not enter any value in the 3
columns I have specified then when the trigger executes the NULL values fail
at trigger execution instead.

Paul

Show quoteHide quote

On Fri, 22 Jul 2005 11:53:07 -0700, Dr. Paul Caesar - CoullByte (UK)
Limited wrote:

Hi Paul,

Exactly.

EM does nothing fancy - the values you enter are formed into an INSERT
(or UPDATE, as appropriate) statement, that then gets sent to the
server. If you don't supply a value, EM will either add a NULL argument
to the INSERT statement, or omit the column from the column list. In
both casees, the comments from my previous message apply.

If you want to continue using EM for data entry (which you really
shouldn't, but that's another discussion), then you'll have to type some
dummy placeholder value in the data grid. It won't be used; your trigger
will replace it with the correct value. You might need to refresh after
each entry (not sure if EM notices this kind of change by itself - if it
does, it'll refresh automatically; if not, you'll have to do it
manually)

Best, Hugo

Hi,

I think I will just keep the soulution I have designed as this workes fine,
I don't plan to use EN for data entry as I have a .NET project under design
to access the tables I have designed.

Thanks

Paul

Show quoteHide quote

obtaining the ip address of connection
Use a stored procedure from a function
triggers
User Defined Function
Tenth Costliest Product!