none
Not able to create a trigger to auto populate datetime value in a field in SQL Server after Insert,update RRS feed

  • Pergunta


  • CREATE TRIGGER DER.Autopopulate_Date
    ON [Schema].Table1
    AFTER INSERT,UPDATE
    AS BEGIN
    DECLARE @todate1 datetime = DER.dReturnDate(getdate())
    SET Last_Changed_Date = convert(datetime, convert(varchar, @todate1, 120), 120);

    Getting the error message as 

    Msg 102, Level 15, State 1, Procedure Autopopulate_Date, Line 6 [Batch Start Line 0]
    Incorrect syntax near '='.
    • Editado Sathu-Kumar quinta-feira, 28 de novembro de 2019 04:52
    quinta-feira, 28 de novembro de 2019 04:44

Todas as Respostas

  • >>SET Last_Changed_Date = convert(datetime, convert(varchar, @todate1, 120), 120);

    Perhaps you need to declare a variable like declare @Last_Changed_Date DATETIME=convert(datetime, convert(varchar, @todate1, 120), 120);



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    quinta-feira, 28 de novembro de 2019 05:36
    Usuário que responde
  • SET Last_Changed_Date = convert(datetime, convert(varchar, @todate1, 120), 120);

    The SQL Syntax in that line is wrong, that's why you get the error and what's "Last_Changed_Date", a column in Table1?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    quinta-feira, 28 de novembro de 2019 07:14
  • Yes, Last_Changed_Date is the column name in Table1
    quinta-feira, 28 de novembro de 2019 23:53
  • Hi Sathu-Kumar,

    Would you like to update table1 ? If so , please try following script.

    update a 
    SET Last_Changed_Date = convert(datetime, convert(varchar, @todate1, 120), 120)
    from table1 a

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Sugerido como Resposta Naomi NModerator domingo, 1 de dezembro de 2019 21:00
    • Não Sugerido como Resposta Naomi NModerator domingo, 1 de dezembro de 2019 21:00
    sexta-feira, 29 de novembro de 2019 06:07
  • Hi Sathu-Kumar,

    Would you like to update table1 ? If so , please try following script.

    update a 
    SET Last_Changed_Date = convert(datetime, convert(varchar, @todate1, 120), 120)
    from table1 a

    No, don't try it, because it is flat out wrong. It will update every row in the table. It has to be something like tihs:

    update a 
    SET Last_Changed_Date = @todate1
    from table1 a
    where exists (select *
                  from   inserted i
                  where  i.keycol = a.keycol)
     

    I've also removed the convert - I can't see what purpose they fill.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    sexta-feira, 29 de novembro de 2019 23:13
  • We hate triggers and regretted putting them into the SQL standards. They are highly procedural, and prevent a lot of optimizations. The only reason they exist is that the original SQL standards were written on top of existing file structures, which were designed for sequential processing. We just didn't have the tools for the declarative programming style that we use in SQL today. 

    However, why are you converting a temporal data type into a COBOL style string? Why aren't you using "DEFAULT CURRENT_TIMESTAMP" in the DDL that you did not post for us? 

    A column named "last_changed_date" is metadata that should never be in the database itself. It's an audit trail data element. If you put it in the database that is being audited, when the database is corrupted or deleted, your audit trail is destroyed too. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    domingo, 1 de dezembro de 2019 18:10
  • I don't think you need a trigger for INSERT. Just use default value for the column.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 1 de dezembro de 2019 21:02
    Moderador