September 24, 2014 at 12:10 pm
I am trying to understand some triggers we have that date back many years.
We have an application that tracks phone calls. We have these old PCs running in remote offices that track phone calls, we have a central database wee the data is imported each night.
When the admin updates an employee's info in the central database, triggers get fired to update the corresponding remote PC depending on whether it is a new employee, or an update to an existing employee.
My initial question is how a certain table is created. INSERTED is a table all the triggers refer to that i cannot figure out its origin anywhere in the DB.
If anyone can provide some guidance on where this 'temp' table may be created i'd appreciate ti.
USE [CAS]
GO
/****** Object: Trigger [dbo].[InitializeAttend] Script Date: 09/24/2014 13:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[InitializeAttend] ON [dbo].[EMP]
FOR INSERT
AS
/*
WARNING:
you must execute this every time you modify this trigger:
sp_settriggerorder @triggername='InitializeAttend', @order='first', @stmttype='INSERT'
check to make sure the value returned from this is 1:
select objectproperty(object_id('InitializeAttend'), 'ExecIsFirstInsertTrigger')
*/
Begin
insert dbo.ATTENDANCE
select distinct inserted.empnum, convert(char,(dbo.ATTENDANCE.[date]), 101), '1'
from dbo.ATTENDANCE inner join
inserted on datediff(dd, isnull(inserted.date_effective, (select max(dbo.attendance.date) from dbo.attendance)), dbo.ATTENDANCE.[date]) >= 0 --this means attendance table date >= date_effective
--if no inserted effective date, match on the last attendance date because automatic employee adds happen for the previous business day's calls
where not(inserted.dept not like '%itp'
and inserted.dept not like '%engp'
and inserted.dept not like '%itc'
and inserted.dept not like '%engc'
and inserted.dept not like '%itrecc'
and inserted.dept not like '%itsalc'
and inserted.dept not like '%engrecc'
and inserted.dept not like '%engsalc'
and inserted.dept not like '%berkeley'
and inserted.dept not like '%itnet'
and inserted.dept not like '%jes'
and inserted.dept not like '%jpp') --include only these suffixes
End
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[InitializeAttend]', @order=N'First', @stmttype=N'INSERT'
September 24, 2014 at 12:48 pm
Inserted and Deleted tables are virtual tables that exist only under the scope of each trigger. They contain the rows affected and have the same structure as the table used (in your example dbo.EMP).
You can read more about them in here:
http://msdn.microsoft.com/en-us/library/ms191300.aspx
Even if 2000 is not an available version, the concepts shouldn't change.
September 25, 2014 at 9:41 am
TY....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply