Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KevinKembel
Starting Member
11 Posts |
 Posted - 2008-06-03 : 13:27:44
|
| I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)For example.CREATE TABLE [Employee] (Id int, Name varchar(50))CREATE TABLE [Company] (Id int, Name varchar(50))CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)CREATE VIEW [dvEmployee]AS SELECT * FROM [Employee] INNER JOIN [EmployeeCompany] ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]CREATE TRIGGER [dvEmployeeUpdate] ON [dbo].[dvEmployee] INSTEAD OF UPDATE ASBEGIN UPDATE EmployeeCompany SET Status = INSERTED.Status FROM EmployeeCompany, INSERTED WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId AND EmployeeCompany.EmployeeId = INSERTED.EmployeeIdENDBecause the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-03 : 13:31:27
|
| It is not possible to "lose records" because of using a keyword in a trigger. Only a DELETE or TRUNCATE TABLE can cause rows to be deleted. Run SQL Profiler to determine what is happening. Filter on TextData like '%DELETE%'.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 13:32:19
|
| Nope. It wont cause a deletion of entire record. Are you sure there are not any other processes causing deletion. You could put a trigger to capture who/what process is causing deletion by writing code to put deleted values along with account causing it to HISTORY table. |
|
|
|
|
|
|
|
|
sql keywords deleting records?