پرس و جو از گذشته: جداول زمانی در SQL Server و PostgreSQL

پرس و جو از گذشته: جداول زمانی در SQL Server و PostgreSQL

“سفر زمان فقط علمی تخیلی نیست – در SQL ، این یک ویژگی است.”

در سیستم های داده مدرن ، تغییرات حسابرسی و ردیابی با گذشت زمان برای:

• انطباق (به عنوان مثال ، GDPR ، HIPAA)
• گزارش تاریخی
• به آرامی ابعاد را تغییر می دهد (SCD نوع 2)
• بازگشت و اشکال زدایی

میزهای زمانی شما را فعال کنید داده های خود را همانطور که در گذشته بود پرس و جو کنید، بدون ایجاد منطق ردیابی تاریخچه سفارشی.

این پست از نحوه استفاده از ویژگی های زمانی در:

• SQL Server (جداول با وضوح سیستم)
• postgresql (محرک + جداول حسابرسی)

مرحله 1: طرح زمانی خود را تعریف کنید

بیایید یک Employees جدول با ردیابی تاریخ تغییر کامل.

سرور SQL (بومی)

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name NVARCHAR(100),
  Title NVARCHAR(100),
  ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
  ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

✅ سرور SQL به طور خودکار یک جدول تاریخچهبشر

postgresql (دستی)

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  title TEXT
);

CREATE TABLE employees_history (
  id INT,
  name TEXT,
  title TEXT,
  valid_from TIMESTAMP,
  valid_to TIMESTAMP
);

مرحله 2: تغییر ورود به سیستم در postgresql

از یک ماشه برای ردیابی دستی تغییرات استفاده کنید:

CREATE OR REPLACE FUNCTION log_employee_update()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO employees_history
  SELECT OLD.*, now(), now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_emp_update
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_employee_update();

✅ محرک ها مسیرهای حسابرسی ایجاد می کنند که از رفتار زمانی تقلید می کنند.

مرحله 3: ایالت های گذشته را پرس و جو کنید

سرور SQL

-- Snapshot at a specific time
SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2024-01-01T00:00:00';

-- Range of time
SELECT *
FROM Employees
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-03-01';

پس از

SELECT * FROM employees_history
WHERE valid_from <= '2024-01-01' AND valid_to > '2023-12-01';

از مورد استفاده کنید: حسابرسی نقش تغییرات را تغییر دهید

بیایید بگوییم که می خواهیم برای یک کارمند به نام حوا تغییر نقش داشته باشیم:

SELECT *
FROM employees_history
WHERE name = 'Eve'
ORDER BY valid_from;

✅ دقیقاً چگونه او را ببینید title با گذشت زمان تغییر کرد

مبادله

بهترین روشها

• سازگار استفاده کنید ValidFrom / ValidTo ستون
• منطق ماشه را در توابع برای قابلیت حفظ خودکار خودکار کنید
• بطور دوره ای تاریخ قدیمی بایگانی
• محافظت از تاریخ از دستکاری کاربر

افکار نهایی: یک جدول زمانی در SQL بسازید

جداول موقتی به پایگاه داده شما می دهند خاطره – به شما اجازه می دهد:

• ببینید چه چیزی تغییر کرده است ، چه زمانی و توسط چه کسی
• به عکسهای فوری شناخته شده خوب برگردید
• تاریخچه را بدون میزهای تولید نفخ پیگیری کنید

“داده های شما سزاوار یک حافظه است. با جداول زمانی ، یکی از آنها را دارد.”

#sql #temporaltables #Auditing #history #datagovernance #sqlserver #postgresql