locked
Default Value or Binding with custom scalar-type function RRS feed

  • Question

  • Hi

     

    I created a custom scalar-valued function, which takes 1 parameter and then returns a result.

    I set a field to point to the scalar-valued function as a Default Value or Binding with a hard coded parameter and that all works fine.

    How do I set the parameter to be the value of another field in the same record instead of a hard coded value?

    In other words, I want the hard coded string nvarchar(50) parameter value that I am passing in to be replaced with another field's value of the same record dynamically.

    I want to be able to do something like this is the Default Value or Binding for the field:  
    ([dbo].[GenerateInvoiceNumber](=DynamboUsername))

    Where =DynamboUsername is the value of the DynamboUsername field for the same row being inserted.

    As explained above, this currently works as hard coded:  
    ([dbo].[GenerateInvoiceNumber]('edward'))

    I am using Microsoft SQL Server 2008 Express Edition.


    I hope this makes sense?

    Thanking you for your help advance.


    Edward
    Wednesday, December 30, 2009 3:09 PM

Answers

  • Hi Edward,

    As far as I know, this is not possible.

    You'll have to use a trigger to set the column to dbo.GeneratInvoiceNumber(DynamboUsername) in all inserted rows.
    -- Hugo Kornelis, SQL Server MVP
    • Marked as answer by NetSpike Wednesday, December 30, 2009 6:37 PM
    Wednesday, December 30, 2009 3:18 PM

All replies

  • Hi Edward,

    As far as I know, this is not possible.

    You'll have to use a trigger to set the column to dbo.GeneratInvoiceNumber(DynamboUsername) in all inserted rows.
    -- Hugo Kornelis, SQL Server MVP
    • Marked as answer by NetSpike Wednesday, December 30, 2009 6:37 PM
    Wednesday, December 30, 2009 3:18 PM
  • Thank you!!!

    I created a trigger and it works great!
    Edward
    Wednesday, December 30, 2009 6:37 PM
  • Hi Edward,

    As far as I know, this is not possible.

    You'll have to use a trigger to set the column to dbo.GeneratInvoiceNumber(DynamboUsername) in all inserted rows.
    -- Hugo Kornelis, SQL Server MVP

    Could you give more details? I'm still studying it, Thanks very much!
    Saturday, January 15, 2011 1:52 AM
  • Hi Laurence,

    Here is an example that uses a user-defined function to compute a default value:

    CREATE TRIGGER ComputeDefault
    ON YourTable AFTER INSERT
    AS
    BEGIN;
      UPDATE   YourTable
      SET      ColumnWithDefault = dbo.YourFunction(InputColumn)
      WHERE EXISTS
       (SELECT *
        FROM   inserted AS i
        WHERE  i.PrimaryKeyColumn1 = YourTable.PrimaryKeyColumn1
        AND    i.PrimaryKeyColumn2 = YourTable.PrimaryKeyColumn2);
    END;

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, January 16, 2011 6:47 PM
  • So this is a recursive trigger. Does it go in infinite cycle?

    Is it really good way to assign default value? You have to make two database operations instead of one.

    I think trigger instead of update can help, but you have to write much more code. (recreate original insert, add your values for fields and run it.) 

    Does anybody know which way is the best one for a such simple operation?

    Friday, May 25, 2012 5:23 AM
  • So this is a recursive trigger. Does it go in infinite cycle?

    Is it really good way to assign default value? You have to make two database operations instead of one.

    I think trigger instead of update can help, but you have to write much more code. (recreate original insert, add your values for fields and run it.) 

    Does anybody know which way is the best one for a such simple operation?

    No, its not recursive. The trigger is after INSERT and the operation executed in the trigger is an UPDATE.

    For assigning a default from a user-defined function, I know no better way than this. However, if the column with the default value is also supposed to never change, it is not truly a default (a standard value that can be changed). In that case, you can use a computed column instead; the syntax of a computed column does allow you to use a user-defined function that takes one or more of the other columns as argument.

    But for a true default, this is (as far as I know) the best you can do.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

    Friday, May 25, 2012 6:49 AM
  • Thanks for the answer!

    Ok If I have to set value for update 

    State1_Date = GetDate() if (inserted.state = 2 and deleted.state = 1)

    State2_Date = GetDate() if (inserted.state = 3 and deleted.state = 2)

    Also I've tried two variants  for ( instead of trigger - ordinary trigger and clr so the code is here. Or I'd better use after trigger ?

    create trigger [dbo].[DOCUMENT_CALC] on [dbo].[DOCUMENT]
     instead of insert, update as
    begin
    	merge document D
    	 using (SELECT i.[OID]
          ,i.[NAME]
          ,i.[PARENTID]
          ,i.[DOCTYPE]
          ,i.[STATEID]
          ,i.[DOCDATE]
          ,i.[MOVEDATE]
          ,i.[CURRID]
          ,i.[CONTRACTID]
          ,i.[AGENTID]
          ,i.[FIRMID]
          ,i.[STOCKID]
          ,i.[TOSTOCKID]
          ,i.[AMOUNT]
          ,i.[NDSAMOUNT]
          ,i.[ALLAMOUNT]
          ,i.[PAIDAMOUNT]
          ,i.[CMT_STATUS]
          ,i.[MANAGERID]
          ,i.[DESCR]
          ,i.[PLANDATE]
          ,i.[CARGORECEIVEID]
          ,i.[OUTDATE]
          ,i.[CREDIT_DAYS]
          ,i.[CURRENCY_RATE]
          ,i.[DEPARTID]
          ,i.[BUDGETID]
          ,i.[PRICETYPEID]
          ,i.[DISCOUNT_PERCENT]
          ,i.[CARGOSENDERID]
          ,i.[CARID]
          ,i.[CLAIMSTOCKID]
          ,i.[DELIVERY_TYPE]
          ,i.[BUHDATE]
          ,i.[COMMENT]
          ,i.[CURRRATE_DATE]
          ,i.[FREIGHT_ORG_ID]
          ,i.[FREIGHT_PAY_ID]
          ,i.[PERIODID]
          ,i.[PROV_SYSDATE]
          ,i.[PROV_SYSDATE2], d.[stateid] as dstateid
          from inserted i left join deleted d on i.OID = d.OID) as source on d.oid = source.oid
          when matched then update 
          SET 
           [NAME] = source.NAME 
          ,[PARENTID] = source.PARENTID
          ,[DOCTYPE] = source.DOCTYPE 
          ,[STATEID] = source.STATEID  
          ,[DOCDATE] = source.DOCDATE  
          ,[MOVEDATE] = source.MOVEDATE
          ,[CURRID] = source.CURRID
          ,[CONTRACTID] = source.CONTRACTID
          ,[AGENTID] = source.AGENTID
          ,[FIRMID] = source.FIRMID
          ,[STOCKID] = source.STOCKID
          ,[TOSTOCKID] = source.TOSTOCKID
          ,[AMOUNT] = (select sum(amount) from document_detail where docid = source.[OID])
          ,[NDSAMOUNT] = (select sum(ndsamount) from document_detail where docid = source.[OID])
          ,[ALLAMOUNT] = (select sum(allamount) from document_detail where docid = source.[OID])
          ,[PAIDAMOUNT] = source.PAIDAMOUNT
          ,[CMT_STATUS] = source.CMT_STATUS
          ,[MANAGERID] = source.MANAGERID
          ,[DESCR] = source.DESCR
          ,[PLANDATE] = source.PLANDATE
          ,[CARGORECEIVEID] = source.CARGORECEIVEID
          ,[OUTDATE] = source.OUTDATE
          ,[CREDIT_DAYS] = source.CREDIT_DAYS
          ,[CURRENCY_RATE] = source.CURRENCY_RATE
          ,[DEPARTID] = source.DEPARTID
          ,[BUDGETID] = source.BUDGETID
          ,[PRICETYPEID] = source.PRICETYPEID
          ,[DISCOUNT_PERCENT] = source.DISCOUNT_PERCENT
          ,[CARGOSENDERID] = source.CARGOSENDERID
          ,[CARID] = source.CARID
          ,[CLAIMSTOCKID] = source.CLAIMSTOCKID
          ,[DELIVERY_TYPE] = source.DELIVERY_TYPE
          ,[BUHDATE] = source.BUHDATE
          ,[COMMENT] = source.COMMENT
          ,[CURRRATE_DATE] = source.CURRRATE_DATE
          ,[FREIGHT_ORG_ID] = source.FREIGHT_ORG_ID
          ,[FREIGHT_PAY_ID] = source.FREIGHT_PAY_ID
          ,[PERIODID] = source.PERIODID
          ,[PROV_SYSDATE] = case when source.stateid = 'O' and source.dstateid < 'O' then CURRENT_TIMESTAMP else source.[PROV_SYSDATE] end
          ,[PROV_SYSDATE2] = case when source.stateid = 'V' and source.dstateid < 'V' then CURRENT_TIMESTAMP else source.[PROV_SYSDATE2] end
    
          when not matched then insert 
          values ( source.[OID], source.[NAME]      ,source.[PARENTID]
          ,source.[DOCTYPE]      ,source.[STATEID]
          ,source.[DOCDATE]      ,source.[MOVEDATE]
          ,source.[CURRID]      ,source.[CONTRACTID]
          ,source.[AGENTID]      ,source.[FIRMID]
          ,source.[STOCKID]      ,source.[TOSTOCKID]
          ,source.[AMOUNT]      ,source.[NDSAMOUNT]
          ,source.[ALLAMOUNT]      ,source.[PAIDAMOUNT]
          ,source.[CMT_STATUS]      ,source.[MANAGERID]
          ,source.[DESCR]      ,source.[PLANDATE]
          ,source.[CARGORECEIVEID]      ,source.[OUTDATE]
          ,source.[CREDIT_DAYS]      ,source.[CURRENCY_RATE]
          ,source.[DEPARTID]      ,source.[BUDGETID]
          ,source.[PRICETYPEID]      ,source.[DISCOUNT_PERCENT]
          ,source.[CARGOSENDERID]      ,source.[CARID]
          ,source.[CLAIMSTOCKID]      ,source.[DELIVERY_TYPE]
       ,source.[BUHDATE]      ,source.[COMMENT]
          ,source.[CURRRATE_DATE]      ,source.[FREIGHT_ORG_ID]
          ,source.[FREIGHT_PAY_ID]      ,source.[PERIODID]
          ,null,null);
      end
    
    public partial class Triggers
    {
        // Enter existing table or view for the target and uncomment the attribute line
         [Microsoft.SqlServer.Server.SqlTrigger (Name="document_set_values", Target="document", Event="INSTEAD OF INSERT, UPDATE")]
        public static void document_set_values()
        {
            SqlConnection conn = new SqlConnection("context connection=true");
            conn.Open();
            try
            {
                SqlCommand sqlCmd = conn.CreateCommand();
                sqlCmd.CommandText = "select * from inserted";
                SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.SchemaOnly);
                string strMerge = "merge document dc using (select {0} d.[stateid] as dstateid, i.oid from inserted i left join deleted d on i.OID = d.OID) as source on dc.oid = source.oid " +
                                  "when matched then update SET {1} when not matched then insert ({2}) values ({3});";
                string sqlFields0 = "", sqlFields1 = "", sqlFields2 = "", sqlFields3 = "";
                for (int i = 0; i < SqlContext.TriggerContext.ColumnCount; i++)
                {
                    if (SqlContext.TriggerContext.IsUpdatedColumn(i))
                    {
                        string cname = sqlReader.GetName(i);
                        //if ( cname.CompareTo("oid") != 0)
                        if (cname != "OID") { sqlFields0 = sqlFields0 + "i." + cname + ","; }
                        sqlFields1 = sqlFields1 + cname + "= source." + cname + ",";
                        sqlFields2 = sqlFields2 + cname + ",";
                        sqlFields3 = sqlFields3 + "source." + cname + ",";
                    }
                }
                sqlReader.Close();
    
                sqlCmd.CommandText = String.Format(strMerge, sqlFields0, sqlFields1.Substring(0, sqlFields1.Length - 1), sqlFields2.Substring(0, sqlFields2.Length - 1),
                      sqlFields3.Substring(0, sqlFields3.Length - 1));
                SqlContext.Pipe.ExecuteAndSend(sqlCmd);
               // SqlContext.Pipe.Send(sqlCmd.CommandText);
            }
            finally
            {
                conn.Close();
            }
    
            
        }
    

    Friday, May 25, 2012 7:14 AM
  • I would definitely not use a CLR trigger. I've never seen any use for them, and this is no exception. Plus, what you are doing (dyamically building a column list) exposes you to a (admittedly quite borderline) SQL injection vulnerability.

    I would also avoid the instead of trigger. Only use them if you really want to do something different than the original action; it just makes no sense to write "instead of" to prevent an insert, update, or delete from happening and then write an awful lot of code to make that same thing happen again, just with a slight modification.

    I think you should go with a regular, T-SQL, AFTER trigger. For the UPDATE trigger, include a test on UPDATE(columname) to prevent endess recursion (and to save performance if the trigger doesn't have to do anything anyway). Now, your requirements are not exactly clear. In the text, you say that you have to set two fields on the change of a state column from 1 to 2 and from 2 to 3. That suggest that no insert trigger is required (as the state can never change on an insert). But the text of the trigger tests for different state codes ('O' and 'V') and only tests the previous state to be alphabetically smaller (which includes newly added rows, as NULL sorts first).

    If your first description is accurate (set date when state switches from 1 to 2 and from 2 to 3), I'd use something like this (untested):

    CREATE TRIGGER DocumentCalc ON dbo.Document
    AFTER UPDATE
    AS
    BEGIN;
      -- Bail out if no rows were affected
      -- NOTE: This statement MUST be first in the trigger!
      IF @@ROWCOUNT = 0 RETURN;
      -- Only need to do work if State column was updated
      IF NOT UPDATE(StateID) RETURN;
      -- Modify SysDate columns where appropriate
      -- Instead of MERGE, you can use UPDATE, but then you have to choose: either multiple subqueries, or the non-ANSI compliant (and somewhat dangerous) UPDATE FROM.
      MERGE INTO Document AS doc
      USING (SELECT     i.OID,
                        i.StateID AS iStateID,
                        d.StateID AS dStateID
             FROM       inserted AS i
             INNER JOIN deleted  AS d
                   ON   d.OID = i.OID
                   AND  d.StateID <> i.StateID) AS src
         ON doc.oid = src.oid
      WHEN MATCHED
      THEN UPDATE
           SET Prov_SysDate = CASE WHEN src.iStateID = 2 AND src.dStateID = 1 THEN CURRENT_TIMESTAMP ELSE src.Prov_SysDate END
             , Prov_SysDate2 = CASE WHEN src.iStateID = 3 AND src.dStateID = 2 THEN CURRENT_TIMESTAMP ELSE src.Prov_SysDate2 END;
    END;

    If the actual requirement is to set the prov_sysdate columns to the current date/time when the state changes to 2 or 3 (and the old value is not important), then you can remove the AND src.dStateID = 1/2 test from the CASE expressions. You still need to check for an actual change; the IF UPDATE function only tells you that the StateID appears somewhere in the SET clause of the UPDATE, nothing else.

    Also, if the actual requirement is to set the sysdate when the state becomes 2/3 (regardless of old value), then you will also need an insert trigger. I would use a seperate trigger instead of a single combined insert/update trigger. This trigger would probably look somewhat like this:

    CREATE TRIGGER ins_DocumentCalc ON dbo.Document
    AFTER INSERT
    AS
    BEGIN;
      -- Bail out if no rows were inserted
      -- NOTE: This statement MUST be first in the trigger!
      IF @@ROWCOUNT = 0 RETURN;
      -- Modify SysDate columns where appropriate
      UPDATE Document
      SET Prov_SysDate = CASE WHEN StateID = 2 THEN CURRENT_TIMESTAMP ELSE Prov_SysDate END
        , Prov_SysDate2 = CASE WHEN StateID = 3 THEN CURRENT_TIMESTAMP ELSE Prov_SysDate2 END
      WHERE EXISTS
       (SELECT *
        FROM   inserted AS i
        WHERE  i.oid = Document.oid);
    END;
    


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

    Friday, May 25, 2012 8:49 AM
  • Works great for me. I have a column that needs a default value of 1 year from today's date that must include leap year:
    (GetDate()+[dbo].[fn_GetNbrDaysInYear](DatePart(Year,GetDate())+(1)))
    Tuesday, September 27, 2016 2:52 PM