استفاده از تریگر؟؟؟؟؟

[جوان ایرانی]

باسلام
می خواهم با استفاده از تریگر تغییراتی که در برخی از جداولم انجام میشه مثل delete insertو.. رو
در جدولی به نامlog نشان بدهم
اما نمی دونم چطور این کار را انجام بدم چه فیلدهایی باید برای log بذارم تا مشخص باشه چه جدولی وچه فیلداهیی ازآ« اون تغییر کرده وچه تغییری کرده اند وچگونه کد تریگرش را بنویسم
ممنون میشم کمکم کنید

[بهزادصادقی]

با سلام،

آیا شما اصلا می‌دانید یک تریگر چه جوری کار می‌کنه؟ مثلا، آیا می توانید یک تریگر بنویسید که در صورت insert در یک چدول خاص فراخوانی می‌شود؟ فرض کنید یک جدول وجود دارد که تعریفش این است:

create table JavanIrani
(
ID int identity(1,1) primary key,
Name nvarchar(100) not null
)


شما می توانی یکه insert trigger برای این بنویسی؟

اگر تونستی، من به شما نشان خواهم داد چه جوری عملیات log را در درون آن تریگر انجام دهی.

[جوان ایرانی]

فک کنم باید به صورت زیر براش تریگر بنویسم
CREATE TRIGGER dbo.testtrigger
ON JavanIrani
AFTER Insert
AS
BEGIN
Insert Insto dbo.log Select *from Inserted
END

ولی اینطوری فقط بلدم تغییرات یه جدول رو ثبت کنم یه نمیدونم چطوری جدول لاگم که قراره تغییرات رو ثبت کنه رو انعطاف پذیر طراحی کنم

[بهزادصادقی]

شما گزینه‌های زیادی برای انعطاف جدول log خود نداری.

اگر می‌خواهی در ازای هر سطر که به جدول JavanIrani اضافه می‌شود، یک سطر در جدول log داشته باشی که همه ستون‌های سطر اضافه شده به جدول JavanIrani در آن ذخیره شده باشد، مجبور هستی که تمام ستون‌های جدول JavanIrani را عینا در جدول log بیاری. در چنین حالتی، شما از جدول log فقط برای ذخیره تغییرات جدول JavanIrani می‌توانی استفاده کنید. در نتیجه، بهتر است که اسم آن را به جای log یه چیزی مانند Log_JavanIrani بگذارید.

اگر ستون‌های جدول Log_JavanIrani دقیقا عین ستون‌های جدول JavanIrani باشد (یعنی هیچ ستون اضافه دیگری نداشته باشد)، اون وقت شما به چند تا مشکل بر می‌خوری. مثلا، با نگاه کردن به یک سطر خاص در جدول Log_JavanIrani، شما نمی‌‌توانید بدانید که این سطر کی به جدول JavanIrani اضافه شده بوده. یا اینکه کدام user یا login این سطر را اضافه کرده. معمولا، در یک همچین شرایطی، دیتابیس کارها یک یا چند ستون دیگر به جدول log خود اضافه می‌کنند که اطلاعات مورد نیازشان را در آن ذخیره کنند.

مثلا، خیلی وقت‌ها، نیاز است که لیست همه تغییرات، چه insert، چه update و یا چه delete در جدول log ثبت شود. در این صورت، باید یک ستون به این جدول اضافه نمود که نشان می‌دهد کدام یک از آن دستورها باعث به وجود آمدن سطر حاضر در جدول log شده است.

می‌توانید کمی بیشتر در مورد نیازهای خود توضیح دهید؟

[Galawij]

تریگر نمی خواد دوست عزیز،
اگر از ورژن 2008 استفاده می کنید، از CDC کمک بگیرید. این هم لینک آموزش (http://barnamenevis.org/showthread.php?321182-%D8%A2%D8%B4%D9%86%D8%A7%DB%8C%DB%8C-%D8%A8%D8%A7-%28CDC-%28Change-Data-Capture-%D8%AF%D8%B1-Sql-Server-2008).

[جوان ایرانی]

ممنون ازتوضیحاتتون
ولی دقیقا مشکل همین جاست که چگونه این دستور را بنویسم یعنی دستور <Inset into db.log select * from Inserted> را باید به چی تغییر بدهم
ودیگر اینکه قراره جدولlog تغییرات چند جدول را ثبت کنه وینی به یه فیلد مثلا بنام <TableName> نیاز داره که نشون بده کدوم جدول تغییر کرده وبرای ثبت این هم نمی دونم چه کدی بنویسم

Galawij عزیز ممنون ولی صورت پروژمه مجبورم از تریگر استفاده کنم

[بهزادصادقی]

جوان ایرانی،

جدول log شما دقیقا باید چه داده‌هایی را در مورد جداول تعییر یافته ذخیره کند؟

[جوان ایرانی]

فیلدهاشون ،نوع تغییر ونام جدول

[tiphooo]

شما قرار نیست فیلدهای جداول دیگر را در جدول Log داشته باشید بلکه در جدول Log یک فیل از نوع text تعریف کنید و تغییرات هر رکورد از هر جدول را در همان فیلد به صورت توضیحات ذخیره کن
مثلا فرض کنید در یک جدول سه فیلد F1,F2,F3 دارید توضیحات را درتریگر به صورت زیر اضافه کنید
توضیحات - درج رکورد در جدول T1 با مقادیر f1=x1,f2=X2,f3=x3
برای Delete و Update هم همینکار را بکن
حذف رکورد از جدول T1 با مقادیر ....
این 3 تریگر تقریبا شکل یکسانی دارند و در هر جدولی که ایجاد می کنید کافیست آنها را کپی کنید حال اینکه می توانید علاوه بر فیلد توضیحات در جدول Log که کل عملیات انجام شده را به صورت Comment ذخیره می کند فیلدهای دیگری نظیر تاریخ و ساعت عملیات ، نام کاربر ، نام جدول را نیز اضافه کنید ولی اصلا به ساخت فیلدهای متناظر فکر نکنید

[Galawij]

شما قرار نیست فیلدهای جداول دیگر را در جدول Log داشته باشید بلکه در جدول Log یک فیل از نوع text تعریف کنید و تغییرات هر رکورد از هر جدول را در همان فیلد به صورت توضیحات ذخیره کن
مثلا فرض کنید در یک جدول سه فیلد F1,F2,F3 دارید توضیحات را درتریگر به صورت زیر اضافه کنید
توضیحات - درج رکورد در جدول T1 با مقادیر f1=x1,f2=X2,f3=x3دوست عزیز فکر نمی کنید، گزارش گیری از چنین جدولی می تونه پیچیده باشه!
به نظر من شما در جدول LOG همانند خود CDC مربوط به SQL کار کنید، معادل هر کدام از فیلدهایی که می خواهید تغییرات مربوط به آن را داشته باشید، یک فیلد متناظر داشته باشید. برای تاریخ و زمان هم از توابع (GetDate, ...) استفاده کنید. برای این که نام جدول هم داخل تریگر به دست بیارید از کد زیر استفاده کنید:
SELECT object_name(parent_id) FROM sys.triggers WHERE name = 'YourTriggerName'در دستور Select هم به نام تک تک فیلدها اشاره کنید.
برای هر یک از سه عملیات استاندارد(Delete, Insert,Update)، یک شماره اختصاص بدید. مثلاً:


Insert معادل 1
Delete معادل 2
Update معادل 3

[بهزادصادقی]

فیلدهاشون ،نوع تغییر ونام جدول

جوان جان،

حالا صحبت جناب tiphooo به کنار، آن چه شما دنبالش هستید به نظر من کاملا منطقی است و در خیلی از سیستم‌ها کاربرد دارد: فیلدهای هر سطری که تغییر کرده، نوع تغییر و نام جدول تغییر یافته.

ولی خوب، میشه حالا یه لحظه به این مسئله دقت کنید: شما می‌خواهید یک، و فقط یک، جدول log داشته باشی که تمام اطلاعات بالا را برای همه جداول دیگر خود نگهداری کند. فرض کن تو سیستم شما، صد جدول وجود دارد که می‌خواهی تغییراتشان را log کنی. و هر کدام از این جداول صد ستون دارند. اگر چدول log می‌بایستی که کل ستون‌های همه چداول تغییر یافته را پوشش دهد، شما مجبوری ده هزار ستون مختلف را در این چدول لحاظ نمایی. که غیر ممکن می‌باشد. می بینی چرا؟ چون چدول log باید همه صد ستون چدول شماره یک را داشته باشد و تمام صد ستون چدول دوم الی آخر.

آیا شما مطمئن هستی تمام ستون های تمام چداول خود را فقط می خواهی در یک چدول log ذخیره کنی؟

اگر می خواهی این کار را بکنی، علاوه بر مشکل بالا، مشکل دیگرت این خواهد بود که دیگر نخواهی توانست از این SYNTAX استفاده کنی:

Insert Insto dbo.log Select *from Inserted

چون وقتی از دستور insert استفاده می‌کنی، فقط وقی می‌توانی از * استفاده کنی که تمام ستون‌های جدولی که از آن داری داده‌ها را کپی می‌کنی دقیقا با همه ستون‌های جدولی که داری توی آن داده‌ها را insert می‌کنی همخوانی داشته باشند. ولی اینجا، جدول inserted تو صد تا ستون دارد، در حالی که جدول log تو ده هزار ستون خواهد داشت. این ستون ها با هم همخوانی نخواهند داشت.

مطمئنی می خواهی فقط از یک چدول log استفاده کنی؟

[in_chand_nafar]

دوست عزيز دو نكته در مورد لاگ در نظر داشته باش
در سيستم هاي اطلاعاتي لاگ به دو حالت در نظر گرفته مي شود
1- لاگ مربوط به عمليات كاربر بدون نگهداري سابقه از ديتاها (تغيير يافته و يا حذف شده)
اين حالت با يك جدول راحت مي تونه پياده سازي بشه فيلدهاي اين جدول عبارتند از
ID : از نوع Identity
SectionCode : نام قسمتي كه عمليات به ازاي آن انجام شده (اطلاعات پرسنل ، انواع x و....)
ActionCode : كد عمليات مورد نظر (افزودن، ويرايش ، حذف و...)
RecNO : شماره سريال ركوردي كه تحت تاثير عمليات قرار گرفته (اين فيلد در واقع مقدار PK مربوط به جدولي است كه عمليات روي آن انجام يافته / در اين حالت همه جداول يه فيلد عددي بايد به عنوان كليد بايد داشته باشند وگرنه بايد ساختار اين جدول را كمي منفجرش كني)
UserID : كد كاربري كه عمليات را انجام داده
ActionDate : تاريخ و زمان انجام عمليات
2- لاگ مربوط به عمليات كاربر با نگهداري سابقه از ديتاها (تغيير يافته و يا حذف شده)
عين ساختار بالا است فقط يك فيلد XML دارد كه در آن سابقه ديتا درج شود به صورت XML بعد با استفاده از XQuery و... مي تواني ازش ديتا بكشي بيرون
اگر خواستي يه تاپيك جديد ايجاد كن تا در موردXML و... نكات ارزنده اي توسط دوستان ارائه شود اين جوري تمامي تغييرات و... داخل يه جدول لاگ ميشه هر موقع هم گزارش خواستي مي توني از اون استفاده كني .و اطلاعات را با استفاده از XQuery بيرون بكشي در ضمن اگر كمي همت كنيد اينجوري مثل CDC تغييرات در سطح سطر و ستون (اضافه شدن فيلد جديد) را ساپورت خواهيد داشت
------
پيشنهاد من در صورت امكان چرخ را اختراع نفرموده و از CDC استفاده شود بر خلاف اينكه خيلي ها فكر مي كنند چون JOB‌دارد روي سيستم بار مي گذارد و... بايد به اطلاع برسانم كه Job اون فقط وظيفه اش اجراي يه برنامه فينگلي كه لاگ فايل مربوط به بانك اطلاعاتي را مانيتور مي كنه و.....

[tiphooo]

ساخت جدول Log متناظر برای هر جدول هزینه زیادی دارد اول اینکه هر فیلدی در جدول اصلی تغییر(حذف یا اضافه یا تغییر نوع) کرد باید در جدول Log هم همینکار را کرد و اینکه تریگرهای متاظر را نیز تغییر داد جستجو در حالتی که بنده توضیح دادم نباید کار سختی باشد وقتی ما اسم جداول را داریم مشکلی نیست در Comment هم کافیست دنبال آن چیزی بگردیم که لازم داریم اگر قرار است همه اطلاعات را وارسی کنیم خوب خود جدول اصلی (منهای حذف) را وارسی می کنیم .بعد هم اینکه Log برای نگهداشتن تغییرات برای زمان بروز مشکل و درک اینکه مشکل از جانب کدام کاربر بوده است می باشد Log که BackUp نیست . البته این نظر شخصی بندست و تمام روشهای Log سازی که بنده مشاهده کرده ام هیچکدام جدول متناظر برای Log نداشته اند و کلا هزینه نگهداری Db در این حالت بالاست و توصیه نمی شود

[Galawij]

این روشی هست که خود مایکروسافت استفاده می کنه، یعنی در CDC معادل هر جدولی، یک جدول دیگر درست می شه. البته می شه یک بانک دیگر معادل آن در نظر گرفت(کاری که بعضی از ابزارها می کنند).
ولی استفاده از نوع داده XML هم پیشنهاد خوبی هست!

[tiphooo]

جالب می شه اگر ما هم کنار جداول مایکروسافت یک جدول دیگر ایجاد کنیم که بشه 3 تا.شما مگر از Log چه انتظاری دارید که می خواهید حجم و ساختار آن را پیچیده تر از خود دیتابیس اصلی طراحی کنید.

[بهزادصادقی]

جالب می شه اگر ما هم کنار جداول مایکروسافت یک جدول دیگر ایجاد کنیم که بشه 3 تا.شما مگر از Log چه انتظاری دارید که می خواهید حجم و ساختار آن را پیچیده تر از خود دیتابیس اصلی طراحی کنید.

من فکر می‌کنم هیچ کس اینجا شکی ندارد که بهترین راه انجام این کار استفاده از امکانات CDC می‌باشد که خود مایکروسافت دقیقا برای این منظور به SQL Server اضافه نموده است. مسئله اینجاست که دوستی که این تاپیک را در وحله اول درج نموده بود، یکی دو بار تصریح نمود که مجبور است از trigger برای حل این مسئله استفاده نماید و در این زمینه درخواست کمک کرد. برای همین هم هست که برخی دوستان دارند روی روش استفاده از trigger برای انجام این کار زوم می‌کنند.

[Galawij]

جالب می شه اگر ما هم کنار جداول مایکروسافت یک جدول دیگر ایجاد کنیم که بشه 3 تا.
جالبیش را ما تعیین نمی کنیم کاربر محترم، این نیاز کسی هست که سوال را مطرح می کند!

شما مگر از Log چه انتظاری دارید که می خواهید حجم و ساختار آن را پیچیده تر از خود دیتابیس اصلی طراحی کنید.
من انتظار خاصی ندارم، دارم به حل سوال این تاپیک کمک می کنم!

[tiphooo]

دوست عزیز من صحبتهای شما را قبول دارم ولی تفکر من اینست که هدف از ایجاد تاپیکها بیشتر برای کمک کردن به حل مسائل شاید نه آنگونه که خود سوال کننده می خواهد بلکه به صورت عملی تر و کاربردی تر و پر مصرف تر باشد. بسیاری از مسائل مطرح شده این تاپیکها برای رفع مشکل برنامه های کاربردی است که شخصی و یا گروه خاصی در حال طراحی برای منظور خاصی هستند . شاید بعضی کارها به صورت عملی و در تجربه هیچ سنخیتی با ایده ال ها در تئوری نداشته باشد ولی گمان من این است قصد این تاپیکها در اختیار گذاشتن تجربیات مفید کاربران در اختیار بقیه (مواردی را که خود تجربه کرده اند و کارایی آن را مشاهده کرده اند ) باشد. اگر به مثال تئوریک پرداخته شود بعید می دانم این تاپیکها نه جذابیتی داشته باشد و نا کارایی.
و دیگر اینکه گاها سوالاتی که اینجا مطرح می شود به عینا خودتان مشاهده کرده اید که سوال کننده دارد بیراهه می رود و شما با استفاده از تجربیاتتان و نظرات خوبتان باید هزینه تقبل برگشت به نقطه صفر و شروع راه درست را به او بقبولانید.

[جوان ایرانی]

از همگی بابت توضیحات ممنون

[Galawij]

دوست عزیز من صحبتهای شما را قبول دارم ولی تفکر من اینست که هدف از ایجاد تاپیکها بیشتر برای کمک کردن به حل مسائل شاید نه آنگونه که خود سوال کننده می خواهد بلکه به صورت عملی تر و کاربردی تر و پر مصرف تر باشد. بسیاری از مسائل مطرح شده این تاپیکها برای رفع مشکل برنامه های کاربردی است که شخصی و یا گروه خاصی در حال طراحی برای منظور خاصی هستند . شاید بعضی کارها به صورت عملی و در تجربه هیچ سنخیتی با ایده ال ها در تئوری نداشته باشد ولی گمان من این است قصد این تاپیکها در اختیار گذاشتن تجربیات مفید کاربران در اختیار بقیه (مواردی را که خود تجربه کرده اند و کارایی آن را مشاهده کرده اند ) باشد. اگر به مثال تئوریک پرداخته شود بعید می دانم این تاپیکها نه جذابیتی داشته باشد و نا کارایی.
و دیگر اینکه گاها سوالاتی که اینجا مطرح می شود به عینا خودتان مشاهده کرده اید که سوال کننده دارد بیراهه می رود و شما با استفاده از تجربیاتتان و نظرات خوبتان باید هزینه تقبل برگشت به نقطه صفر و شروع راه درست را به او بقبولانید.
حرف شما کاملاً متین و به جاست.
من در پست پنج به روش اصولی و از طریق امکانات SQL جواب دادم، که مورد نیاز کاربر نبود. اگر قرار هست فقط یک جدول برای این کار اختصاص داده بشه، من فکر می کنم یک ستون با نوع داده XML مناسبتر از سایر راه کارهای ارائه شده باشد.

[جوان ایرانی]

باتشکر از همگی بابت توضیحات
ولی یه مشکل با این که جدول مورد نظرم که می خوام تغییراتش ثبت بشه رو ساخته ام اما وقتی تریگر رو می نویسم
میگه "این جدول وجود ندارد یا در این عملگر نامعتبر است" کسی می دونه مشکل کجاست؟

یه سوال دیگه با این تابع GetDate فقط تاریخ رو میشه بدست اورد؟برای ساعت چی داریم؟

[جوان ایرانی]

من یه همین چیزی نوشتم ولی طرز استفاده از تابع GetDate رونمی دونستم وهمچنین نمی دونم چه جوری تغیی دهنده رو بیارم
با توجه به اینکه پروژم ارتباط پایگاه داده و زبان برنامه نویسی است نمی دونم این تغییر دهنده رو باید از فرم هام بیارم یا.....
CREATE TRIGGER insQ
ON dbo.Question
AFTER INSERT
AS
BEGIN
begin tran
Insert Into dbo.log Select QuestionID From inserted
Insert Into dbo.log(ChangeType,DatTime,TableName,ChangerID) values('INSERT',Day.now,'Question
',' ')
commit tran
END
GO