One of the things my company does on SQL that is kinda different from other companies and different than i’d do it is when someone isn’t a member of the website any longer, that member is deleted instead of set to inactive or suspended. This has worked for years and years, until now. We’ve started to work with a 3rd party vendor that will handle much of our web content. One of the problems we’ve run into is the 3rd party software needs to know when someone has been deleted or updated on our system. Updated is easy, we have a last updated column, but when you delete a member along with all their records, there isn’t any data saying that person even existed.
So I had to come up with an off the wall way to find deleted members. My first thought was to use triggers, but we are using a view to combine all the members info from different tables and the triggers will not work with a view (someone will probably prove me wrong, oh well). What I decided to do was do a nightly copy of the view to a table, say 10pm. Then in 24 hours (or close) I run a program that grabs a view that checks for a difference in the live up to date view against the table of last nights data. This will show me anyone that has been deleted. I also look at the up to date view for changed or new from 24 hours ago using the last updated column.
SQL code to compare the view and table, with a union to check date on the up to date view..
SELECT *, 'Deleted' AS Changed FROM tblYesterdaysdata WHERE (NOT EXISTS (SELECT * FROM [UpToDateView] WHERE [UpToDateView].Individualid = tblYesterdaysdata.Individualid AND [UpToDateView].IndividualEmailAddr = tblYesterdaysdata.IndividualEmailAddr AND [UpToDateView].Community = tblYesterdaysdata.Community)) UNION SELECT *, 'New or Changed' AS Changed FROM [UpToDateView] WHERE LastUpdated = (CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) - 1)
- SQL Server Difference FAQs-2 (slideshare.net)
- Is there a performance difference between JOIN and WHERE? (mysqlperformanceblog.com)