April 29, 2016 at 11:38 am
I am trying to create a trigger on the emp table and if the empname or depid field is gets updated...The trigger will retroactively updates the emphistory empname or deptid field. I am working to understand what is going on.
/* CREATE TABLE */CREATE TABLE emphistory (DeptIDEmpIDIsActive VARCHAR(100));/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '643331');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '2322431');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '782221');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '754331');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '453221');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '704321');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '7457771');
/* INSERT QUERY */INSERT INTO TABLE_NAME(DeptIDEmpIDIsActive) VALUES( '251221');
/* CREATE TABLE */CREATE TABLE emp(empidempnamedeptid VARCHAR(100));/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '333Ali1550');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '243Joe232');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '222Steve78');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '433Markj75');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '322Dale45');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '432Stephanie70');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '777Kent745');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '122Julius25');
/* INSERT QUERY */INSERT INTO TABLE_NAME(empidempnamedeptid) VALUES( '233Tovarus97');
So far I have had the idea to do this..
CREATE TRIGGER dbo.trg_Updt_empnme ON [dbo].[emp]
AFTER UPDATE
AS
IF UPDATE(deptid)
BEGIN
UPDATE [dbo].[Emphistory]
SET [dbo].[Emphistory].[DeptID] = i.[DeptID]
FROM [dbo].[Emphistory]
JOIN inserted AS i
ON i.[DeptID] = [dbo].[Emphistory].[DeptID] -- use the appropriate column for joining
JOIN deleted AS d
ON i.[DeptID] = d.[DeptID]
AND ( i.[DeptID] <> d.[DeptID]
OR d.[DeptID] IS NULL
) ;
END ;
April 29, 2016 at 11:47 am
i would add dates and whodunnit information to the history table as well. From what you have show so far, you cannot tell when it occurred, which could be important, or who changed the data .
you are only concerned if the deptId changed? mo other changes/columns are worthy of tracking changes?
there's quite a few built in functions you might want to use to track change and whodunnit info, here's my favorite example:
for 2008 and above:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
April 29, 2016 at 12:48 pm
Here is my final lunge at this problem... I would like to think im going in the right direction....
--b.Trigger 2 – Build a tirgger on the emp table after an update of the empname or deptid column - It updates the subsequent empname and/or deptid in the emp_history table.
CREATE TRIGGER trg_Updt_empnme
ON [dbo].[emp]
AFTER UPDATE
AS
DECLARE @EmpID INT,
DECLARE @DeptID INT
SELECT @EmpID = empid FROM inserted
SELECT @DeptID = deptid FROM inserted
IF UPDATE([deptid])
---UPDATE [dbo].[Emphistory]
--SET [deptid] = @DeptID
--FROM [dbo].[Emphistory]
update eh
set eh.[DeptID] = i.[DeptID]
[dbo].[Emphistory] eh
join inserted i on eh.EmpID = i.empid
where eh.empid <>i.empid
INSERT INTO [dbo].[Emphistory]
VALUES (@EmpID,@DeptID,1)
April 29, 2016 at 1:32 pm
What is the insert for? Are you trying to update the history record if an update has occurred or insert a new history record if an insert has occurred? If so see below for an example of what could be done....
Also you mention capturing changes to the empname column but that isn't in your trigger anywhere.
I'm not sure of the logic behind this - where eh.empid <>i.empid
Finally when designing triggers always write them with the assumption that multiple rows will be processed
CREATE TRIGGER trg_Updt_empnme
ON [dbo].[emp]
AFTER UPDATE, INSERT
AS
IF (SELECT COUNT(*) FROM deleted) = 0 --no records in deleted so assume an insert
INSERT INTO [dbo].[Emphistory]
SELECT EmpID, DeptID, 1 FROM inserted;
RETURN;
IF UPDATE([deptid])
UPDATE eh
set eh.[DeptID] = i.[DeptID]
[dbo].[Emphistory] eh
join inserted i on eh.EmpID = i.empid;
April 29, 2016 at 2:10 pm
I want to update the history record if a update has occured. (I have two tables. One emp and the other emphistory. When the empname or Deptid field updates in dbo.emp I want the update to also be reflected in deptid.emphistory by way of a trigger. These are the only requirements. ) Thanks.
April 29, 2016 at 2:38 pm
Thank You... I do not know why my other expected solution did not work for this...I really beat my head against a rock on this one... but I understand the logic behind your implementation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply