באשכול הזה היה דיון אודות פונקציה שמייקרוסופט כנראה לא סיפקו במזיד או שלא במזיד. בכל אופן נאלצנו לעשות קוד שיוצר מחרוזת 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)