none
Trigger to delete records on insert based on a specified column value

    Question

  • Hello,

    There are some tables which contain the date column. I want to ensure that the data contained in the table is for that specific date only. Hence I would like to create a trigger that will ensure that when ever there is an insert operation it will delete the old record for that specific date and insert the new one. 

    Please help how can I achieve this.

    Rajiv


    Wednesday, August 20, 2014 6:41 AM

All replies

  • You can use Merge Statement :

    Merge


    Thanks and regards, Rishabh K

    Wednesday, August 20, 2014 6:49 AM
  • I don't think that's possible with trigger. 

    What would happen if a user inserts two rows with two different dates?

    INSERT INTO <TABLE> (<OtherColumns>,DT) 
    Values (<OtherValues>,20140801),
           (<OtherValues>,20140802),

    Probably you could use the merge statements everywhere to implement this logic, however if a user tries to insert directly to the table there could be issues.

    What could you probably do is to have a trigger to block (and rollback) the transaction if you find invalid records


    Satheesh
    My Blog | How to ask questions in technical forum


    Wednesday, August 20, 2014 6:56 AM
  • Hi Rajiv,

    Please refer to this MSDN article to create a trigger.

    Hope this helps ! Cheers !! 


    Regards, Ram.

    Wednesday, August 20, 2014 6:59 AM
  • If the user performs an INSERT based on SELECT statement like 

    INSERT INTO tbl... SELECT <columns> FROM anothertabe 

    What if the INSERT withing a trigger (inserted virtual table) contains more than one row? 


    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

    Wednesday, August 20, 2014 8:43 AM
    Answerer
  • Hi Satheesh,

    There can be 2 rows with 2 different dates. The objective is to have unique value for date based on another column.

    for example;

    I have a table called as "OnlineStatus" containing columns:

    a. ServerName char(25)

    b. Status char(25)

    c. CheckDate date

    Now, in the estate there are 1000+ servers, hence 1000 rows for one single day. The insertion happens using a PowerShell script. But If the script runs again, it will cause another insert, hence before committing the insertion of data for the same date that already exists in the table, I would like to delete the records and have the new data.

    Thanks

    Rajiv

    Wednesday, August 20, 2014 9:57 AM
  • Wednesday, August 20, 2014 10:45 AM
  • Hi Rajivir,

    Have you try Satheesh's suggestion? If the issue persists, please provide us more information about it, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, September 12, 2014 2:33 AM
    Moderator