Trigger in sql200

  • 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'

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • TY....

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply