0

I'm recording all insert and update on TaskDetail table using a trigger,Now I want to assign multiple staff to a task, But if staff id stored in different child table how can I track audit history, I have considered storing staff id as comma separated values but child table is always a good option.

In TaskStaff table multiple staff will have same taskId

CREATE TRIGGER [dbo].[TaskDetail_History_Trigger]
ON [dbo].[TaskDetail]
FOR Insert,UPDATE
AS

INSERT INTO TaskHistory SELECT * FROM inserted
GO

ALTER TABLE [dbo].[ProductionDetail] ENABLE TRIGGER [Task_History_Trigger]
GO

CREATE TABLE [dbo].[TaskDetail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StaffId] [int] NULL,
CONSTRAINT [PK_ProductionDetail_1] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)

CREATE TABLE [dbo].[TaskHistory](
[HistoryId] [int] IDENTITY(1,1) NOT NULL,
[Id] [int]  NOT NULL,
[StaffId] [int] NULL,
CONSTRAINT [PK_ProductionDetail_1] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)


 CREATE TABLE [dbo].[TaskStaff](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [TaskId] [int] NOT NULL,
  [StaffId] [int] NOT NULL,

CONSTRAINT [PK_ProductionDetailStaff] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)
Shijith
  • 3
  • 1
  • *I have considered storing staff id as comma separated values* Now you have bunch of problems. You should start with database design first. – JohnyL Aug 10 '18 at 08:25
  • I'm not using comma separated values just mentioned audit can be achieved using comma separated values. – Shijith Aug 10 '18 at 08:32
  • You shouldn't consider this - you should eliminate it. – JohnyL Aug 10 '18 at 08:34
  • `But if staff id stored in different child table` what is the name of the `child table` ? – Squirrel Aug 10 '18 at 08:46
  • TaskStaff is name of staff table, After inserting Task staff id will be saved into TaskStaff table with TaskId so its possible to have multiple staff – Shijith Aug 10 '18 at 09:05

0 Answers0