0

I have a table in SQL Server 2008 (MyTable) which contains a field (MyField). MyField cannot take nulls and it will be populated with a default value. The default value is calculated by means of a complex stored procedure (MyStoreProcedure), which invokes other tables and fields in my database. My users should have the option to change this default value once it has been supplied by SQL server. SQL server, however, will have a AFTER UPDATE TRIGGER to check that the entered value in MyField conforms to certain rules.

I have tried to populate MyField with the intended default value by means of a 'TRIGGER AFTER INSERT' and SQL Server tells me that null values cannot be inserted into MyField, which is actually true. Is there a workaround to implement this default value based upon a stored proc?

Thanks

fritesmodern
  • 21
  • 1
  • 1
    The obvious way is to insert new data using a SP rather than a regular insert. Or an instead of trigger, but a SP is clearer to follow. – Dale K Oct 27 '21 at 22:25

1 Answers1

0

This may be a case for an INSTEAD OF INSERT trigger, which will allow you to access the stored procedure and set a default value before the row(s) is/are inserted into the target table.

The trick is then getting the stored procedure result(s) into the trigger. One approach would be to insert the stored procedure results into a temporary table, which then may be joined with the INSERTED meta table for the actual insert. See How to SELECT FROM stored procedure.

T N
  • 4,322
  • 1
  • 5
  • 18
  • Thanks for your help. I have changed my trigger to a 'Instead Of' trigger for INSERT. MyTable, where MyField is, contains a primary called MyPk, an integer, whose default value, always supplied by SQL server, is based upon a seed of 1. If I invoke my trigger with "INSERT INTO" and I run "SELECT I.MyPK FROM INSERTED I" inside the trigger, I.MyPK shows zero. This is a problem as I cannot link the Inserted table with MyTable via the primary key, wihch is unique. – fritesmodern Oct 27 '21 at 23:06
  • @fritesmodern of course `Inserted` won't contain your identity value, because you have told SQL Server that **YOU** are going to insert the records, so it won't waste its time adding these values until you carry out the insert. You therefore need to insert those records and capture the new identity values using the 'OUTPUT' clause. – Dale K Oct 27 '21 at 23:24