Answered by:
Trigger to delete records on insert based on a specified column value

-
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
Question
Answers
-
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
- Marked as answer by Kalman TothModerator Tuesday, September 23, 2014 6:48 AM
All replies
-
-
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
- Marked as answer by Kalman TothModerator Tuesday, September 23, 2014 6:48 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
-
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
-
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
-