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

Answer 1

You can use Merge Statement :

Merge

---

Thanks and regards, Rishabh K

Answer 2

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

Answer 3

Hi Rajiv,

Please refer to this MSDN article to create a trigger.

Hope this helps ! Cheers !! 

---

Regards, Ram.

Answer 4

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

Answer 5

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

Answer 6

So why dont you use a MERGE statement?

http://msdn.microsoft.com/en-IN/library/bb510625.aspx

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 7

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