לאחר מאבקים עזים: קוד שמתעד כל שינוי בדטה בייס
-
באשכול הזה היה דיון אודות פונקציה שמייקרוסופט כנראה לא סיפקו במזיד או שלא במזיד. בכל אופן נאלצנו לעשות קוד שיוצר מחרוזת sql כטקסט, ואז מממש אותה.
כמו כל דבר טוב אני עורך את זה כמה פעמים עד להשלמה. כעת ערכתי את הפונקציה (27/01/14 22:13) על מנת לאפשר גם בטריגר של מחיקה, לשמור את כל השדות של הרשומה הנמחקת למי שירצה.ראשית יש ליצור טבלת לוג כזאת:
CREATE TABLE [dbo].[LogTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Content] [nvarchar](max) NULL, [TableName] [nvarchar](40) NULL, [ColumnName] [nvarchar](50) NULL, [RowID] [int] NULL, [OldValue] [nvarchar](max) NULL, [NewValue] [nvarchar](max) NULL, [UserModified] [nvarchar](100) NULL, [date] [datetime] NULL, CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[LogTable] ADD CONSTRAINT [DF_Log_date] DEFAULT (getdate()) FOR [date] GO ALTER TABLE [dbo].[LogTable] WITH CHECK ADD CONSTRAINT [CK_LogTable] CHECK (([OldValue]<>[NewValue])) GO ALTER TABLE [dbo].[LogTable] CHECK CONSTRAINT [CK_LogTable] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'מאמת שלא נכנסים ערכים שווים לטבלה' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LogTable', @level2type=N'CONSTRAINT',@level2name=N'CK_LogTable' GOלאחר מכן ליצור פונקציה זאת:
CREATE FUNCTION [dbo].[GetSqlStatementToInsertLogTable] ( @PROCID int -- משתנה זה מכיל את האידי של הטריגר הוא מוכנס על ידי משתנה מקומי שיש בכל טריגר ) RETURNS nvarchar(max) AS BEGIN DECLARE @TableName sysname ,@Sql nvarchar(max) = '' , @CurrentRow int = 0 , @CurrentFieldName nvarchar(100) -- מאתר את שם הטבלה שמאחוריה עומד הטריגר שביקש את הפונקציה select @tablename = object_name(parent_id) from sys.triggers where object_id = @PROCID -- טבלה המכילה את כל השדות של טבלה נוכחית declare @FieldsTable table (ColID int, ColumnName nvarchar(50), TableName nvarchar(50)) insert into @FieldsTable select c.column_id as ColID, c.name as ColumnName, t.name as TableName from sys.all_columns c inner join sys.tables t on t.object_id = c.object_id where c.is_identity = 0 and user_type_id <> 189 and t.name = @tablename -- אין טעם להכניס אידנטיטי כי הוא לא משתנה אף פעם כמו כן סוג חותמת זמן לא שייך לתעד declare @maxRecords int = (select max (colid) from @FieldsTable) declare @str nvarchar(max) while @CurrentRow < @maxRecords BEGIN SET @CurrentRow = @CurrentRow + 1 set @CurrentFieldName = (select ColumnName from @FieldsTable WHERE ColID=@CurrentRow) -- השדה הנוכחי בטיפול -- יוצר את האסקיואל שאמור לטפל בכל שדה ושדה והרוצה יעיין בקוד הדק היטב set @str = N'select ''' + @TableName + ''',''' + @CurrentFieldName + ''', d.ID, CONVERT(VARCHAR(max), d.' + @CurrentFieldName + '), CONVERT(VARCHAR(max), i.' + @CurrentFieldName + '), ''' + SYSTEM_USER + ''' from #deleted d left join #inserted i on i.ID = d.ID where i.' + @CurrentFieldName + ' <> d.' + @CurrentFieldName + ' or i.ID is null' + iif(@CurrentRow < @maxRecords , ' union all ', '' ) set @Sql = CONCAT (@Sql, @str); END -- זהו זה RETURN @Sql ENDאחרי שיצרנו את הפונקציה יש להכניס את הקוד הבא בכל טריגר (אין צורך לעשות שום שינוי בקוד הוא נכנס כמות שהוא לכל טריגר שבעולם):
SELECT * INTO #inserted FROM inserted SELECT * INTO #deleted FROM deleted declare @Sql nvarchar(max) = dbo.GetSqlStatementToInsertLogTable (@@PROCID) insert into LogTable (TableName , ColumnName , RowID , OldValue, NewValue ,UserModified) EXECUTE sp_executesql @Sqlשימוש בקוד זה מחייב את המשתמש על סך מיליון שקלים פלוס מע"מ לכל פעולת טריגר... :lol: :lol: :lol: :lol:
פורסם במקור בפורום CODE613 ב27/01/2014 19:45 (+02:00)
-
אפשר גם דוגמא לטריגר ואיפה כותבים אותו בדיוק.
טריגר אתה יכול להעתיק את הקוד, בדרך כלל עושים new trigger בלחיצה ימנית והוא פותח לך חלון כזה:
GO /****** Object: Trigger [dbo].[Contacts_Updated] Script Date: 27/01/2014 21:37:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Contacts_Updated] ON [dbo].[Contacts] after UPDATE AS BEGIN SELECT * INTO #inserted FROM inserted SELECT * INTO #deleted FROM deleted declare @Sql nvarchar(max) = dbo.GetSqlStatementToInsertLogTable (@@PROCID) insert into LogTable (TableName , ColumnName , RowID , OldValue, NewValue ,UserModified) EXECUTE sp_executesql @Sql END GOאגב, את הטבלה יצרתי אבל את הפונקציה לא נתן לי שמור כל פעם הוציא הודעת שגיאה
יש מילה GO בסוף הקוד (תיקנתי את זה עכשיו) תמחק אותה ייתכן שהיא עושה את השגיאה, אם לא אז תגיד איזו הודעת שגיאה הוא כותב.
פורסם במקור בפורום CODE613 ב27/01/2014 21:40 (+02:00)
-
כשאני לוחץ על תיקיית הפונקציות יוצא את התפריט הבא:
ניסיתי להדביק את הקוד בשלושת האפשריות, ואז כאשר ניסיתי לשמור קיבלתי את השגיאה הבאה:
פורסם במקור בפורום CODE613 ב27/01/2014 21:53 (+02:00)
-
תנסה לעשות את זה פשוט עם חלונית new query ולהדביק את הקוד ולעשות אקסקיוט, בלי ללכת לפונקציות. אחר כך תרפרש את התיקיה של הפונקציות ותראה את הפוקנציה שנוצרה.
אגב אתה משתמש בסיקוול סרבר 2012???
כי אני כתבתי אותו ב 2012 אני לא יודע מה גירסאות אחורנית תומכות.
תנסה בכל אופן.פורסם במקור בפורום CODE613 ב27/01/2014 22:29 (+02:00)
