July 11, 2016 at 3:01 pm
I may be going at this all wrong.. I need some fresh eyes.
I have two tables
[dbo].[AttendanceV2](
[aid] [bigint] IDENTITY(1,1) NOT NULL,
[EmpID] [char](9) NOT NULL,
[LastName] [varchar](255) NOT NULL,
[FirstName] [varchar](255) NOT NULL,
[Last4] [char](4) NOT NULL,
[Date] [datetime] NOT NULL,
[Type] [char](1) NOT NULL,
[Points] [real] NOT NULL,
[Notes] [varchar](max) NULL)
and
[dbo].[EmployeesV2](
[eid] [bigint] IDENTITY(1,1) NOT NULL,
[EmpID] [char](9) NOT NULL,
[LastName] [varchar](255) NOT NULL,
[FirstName] [varchar](255) NOT NULL,
[Last4] [char](4) NOT NULL,
I'm not even 100% sure this is the proper layout I want/need.
The empid can be assigned to a new person at some point in the future (not my idea and out of my control)
The combination of empid & last 4 of the social should be pretty much a unique combination.
If a change is made to a record In the AttendanceV2 table, I want those changes to cascade to the matching columns in EmployeesV2 table if it matches the same person.
Which I believe would be if the EmpID and Last4 match.
I played with the trigger below, but when I updated one of the columns, it didn't seem to fire the trigger
I'm guessing this isn't right because I'm updating the row after each cellchange in a datagridview I have in my c# app (which is out of scope for this question I guess)
CREATE TRIGGER tr_AttendanceV2_Update
ON AttendanceV2
AFTER UPDATE
AS
BEGIN
IF UPDATE(LastName) /*If column not affected skip*/
BEGIN
UPDATE c
SET LastName = d.LastName
FROM [AttendanceTracking].[dbo].[EmployeesV2] AS c
JOIN inserted AS i
ON i.[EmpID] = c.[EmpID]
JOIN deleted AS d
ON i.[EmpID] = d.[EmpID]
AND ( i.[EmpID] <> d.[EmpID]
OR d.[EmpID] IS NULL
) ;
END
IF UPDATE(FirstName) /*If column not affected skip*/
BEGIN
UPDATE c
SET FirstName = d.FirstName
FROM [AttendanceTracking].[dbo].[EmployeesV2] AS c
JOIN inserted AS i
ON i.[EmpID] = c.[EmpID]
JOIN deleted AS d
ON i.[EmpID] = d.[EmpID]
AND ( i.[EmpID] <> d.[EmpID]
OR d.[EmpID] IS NULL
) ;
END
IF UPDATE(Last4) /*If column not affected skip*/
BEGIN
UPDATE c
SET Last4 = d.Last4
FROM [AttendanceTracking].[dbo].[EmployeesV2] AS c
JOIN inserted AS i
ON i.[EmpID] = c.[EmpID]
JOIN deleted AS d
ON i.[EmpID] = d.[EmpID]
AND ( i.[EmpID] <> d.[EmpID]
OR d.[EmpID] IS NULL
) ;
END
END;
I think I might just be having trouble getting my requirements in my head for this.
What I want
* I want all additions to AttendanceV2 to exist in the main EmployeesV2 table.
* If someone changes an EmpID in one of the records in AttendanceV2, I want them to cascade to the same userid/last4 in EmployeesV2
AttendanceV2 could have 1 or more instances of a row from EmployeesV2
I'm sure I explained this poorly :crying:... But, maybe someone could help with the right questions to help me onto the right track?
Thanks for your time
July 11, 2016 at 3:12 pm
Sorry, the more I read this, the more odd it sounds. Are you stuck with this table design? It just sounds a little off. I'm not sure i understand the reasoning behind essentially reusing the Employee table's Primary key on multiple records.
I guess I would back up a step. Your basic design is this:
Employee--(1)--Attends--(M)--Event
So the "Attends" table is pretty much
CREATE TABLE Attends (
EmployeeID INT,
EventID INT,
CONSTRAINT AttendsPK PRIMARY KEY (EmployeeID, EventID),
FOREIGN KEY EmployeeFK REFERENCES Employee(EmployeeID),
FOREIGN KEY EventID REFERENCES Events(EventID)
);
The problem with overwriting an employee holding a position is that the Attendance record is specific to an individual and not a role. If you wanted to summarize all the events attended by a person holding a specific role, you'd have to have a RoleHistory table...
What if you had...
JobRole--(1,M)--Employee--(1,M)--Attends--(M,1)--Event
?
Then you could query what events a "Job Role" (collection of people who have held a role/position) have attended. Sounds like your design is a little off. You don't need ANY "Person" information in the "Attends" table at all.
July 13, 2016 at 9:40 am
I think you need to go with something like the following:
EMPLOYEE
(EID INT IDENTITY PRIMARY KEY,
EMPID CHAR(9),
START_DATE DATE,
STOP_DATE DATE,
<other fields>);
ATTENDANCE
(AID INT IDENTITY,
EID INT FOREIGN KEY REFERENCES EMPLOYEE(EID),
<other fields, but don't replicate any columns from EMPLOYEE>);
You can setup your trigger on the EMPLOYEE table so that only one person with a particular EMPID can be active at a time (aka STOP_DATE is NULL). Then you won't have to perform any cascade updates. Just always include the EMPLOYEE table along with the ATTENDANCE table, if you need to display anything about the EMPLOYEE in your output.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply