Help with this trigger

microsoft.public.sqlserver.programming

16 Dec 2005 1:45 AM

dbuchanan

Hello,

I need some help with this trigger.

I am a newbie at Triggers as you will see. I have had difficulty with
some of the details of the concept. My questions are within the text of
my attempt at the trigger.

Here is how it is to work:
When the column "Order" is changed for a record in "tbl040Cmpt" I want
to change the column "Order" in other records in the same table that
have a kind-of association; The one record I will call the 'parent' the
other records I will call the 'children' The 'children' have the Id of
the 'parent' contained in another column named 'cmVarchar06'. They are
not really parents and children have a kind-f associaltion that mimic
it.

Here is my attempt:
\\
CREATE TRIGGER UpdateOrderForSensorChildrenWhenValveIsChanged
ON tbl040Cmpt AFTER UPDATE

DECLARE    @Order_old numeric(5,3),
    @Order_New numeric(5,3),
    @cmVarchar06 varchar(50),
    @cmBit11 bit, -- Valve Action (when coming from the triggering record)
--    @fkComponentType int

-- Get the value of the changed field
    SELECT @Order_old = Order FROM tbl040Cmpt --<<How do I get this value
if the column has already just been updated by a previous stored
procedure?
    SELECT @Order_new = Order FROM tbl040Cmpt --<<Is this how I get the
new value?
    SELECT @cmVarchar06 = fkDevice FROM tbl040Cmpt -- The record that
fires the trigger is an associated 'parent' of its sensors
    SELECT @cmBit11 = cmBit11 FROM tbl040Cmpt -- The value of this column
indicates whether the valve is advance or return (* does advance of
valve mean 'advance' of cyln, * does advance of valve mean 'on' of Air)
--    SELECT @fkComponentType = fkComponentType FROM tbl040Cmpt -- This
will always be 1 for Valve

-- when these conditions are met
Where    fkComponentType = 1 -- Valve
    and @Order_old <> @ Order_new -- does the trigger evaluate all updates
to records in this table even though the order column is not changed?

-- Now copy the Order value to the qualifying records

IF     @cmBit11 = 1     THEN -- ValveActionIsAdvance = TRUE (air service or
cyln sensor)

-- ***** Cylinder sensor update *** Advance ****
Update     tbl040Cmpt
    Set Order = @Order

Where    fkComponentType = 7 -- Motion Sensor ~ (a cylinder sensor)
    and cmVarchar06 = @cmVarchar06 -- a motion sensor associated with the
cylinder
    and cmBit11 = 1 -- Sensor Action is On (for records filtered by the
update criteria)
    and (cmbit05 = 1 -- Adv
    or cmbit04 = 1) -- Almost Adv
-- **************************************

-- ***** Air Service sensor update *** On *******
Update     tbl040Cmpt
    Set Order = @Order

Where    fkComponentType = 3 -- Non-Motion Sensor (a air service sensor)
    and cmVarchar06 = @cmVarchar06 -- from the same parent
    and cmBit11 = 1 -- Non-Motion Sensor is 'On'
-- **************************************

ELSE -- cmBit11 = 0 ~ ValveActionIsAdvance = FALSE (air service or cyln
sensor)

-- ***** Cylinder sensor update *** Return ******
Update     tbl040Cmpt
    Set Order = @Order

Where    fkComponentType = 7 -- Motion Sensor ~ (a cylinder sensor)
    and cmVarchar06 = @cmVarchar06 -- a motion sensor associated with the
cylinder
    and cmBit11 = 1 -- Sensor Action is On (for records filtered by the
update criteria)
    and cmbit01 = 1 -- Rtn
    or cmbit02 = 1 -- Almost Rtn
-- **************************************

-- ***** Air Service sensor update *** Off *******
Update     tbl040Cmpt
    Set Order = @Order

Where    fkComponentType = 3 -- Non-Motion Sensor (a air service sensor)
    and cmVarchar06 = @cmVarchar06 -- from the same parent
    and cmBit11 = 0 -- Non-Motion Sensor is 'Off'
-- **************************************

END IF
//

I have not tried it because the are a couple of basic elements missing.
Please direct me.

Thank you,
dbuchanan

Extra information;

Here is my table:
\\
CREATE TABLE [tbl040Cmpt] (
    [pkComponentId] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF_tbl040Cmpt_pkComponentId] DEFAULT (newid()),
    [fkDevice] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [fkComponentType] [int] NOT NULL ,
    [ComponentDescription] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [QuanSharingConfiguration] [tinyint] NOT NULL ,
    [QuanConfigured] [tinyint] NOT NULL CONSTRAINT
[DF_tbl040Cmpt_QuanConfigured] DEFAULT (0),
    [QuanActuations] [tinyint] NOT NULL CONSTRAINT
[DF_tbl040Cmpt_QuanActuations] DEFAULT (1),
    [cmVarchar01] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [cmVarchar02] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [cmVarchar03] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [cmVarchar04] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [cmVarchar05] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [cmVarchar06] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [cmNumeric01] [numeric](5, 3) NULL ,
    [cmNumeric02] [numeric](5, 3) NULL ,
    [cmNumeric03] [numeric](5, 3) NULL ,
    [cmNumeric04] [numeric](5, 3) NULL ,
    [cmNumeric05] [numeric](5, 3) NULL ,
    [cmNumeric06] [numeric](5, 3) NULL ,
    [cmNumeric07] [numeric](5, 3) NULL ,
    [cmBit01] [bit] NULL ,
    [cmBit02] [bit] NULL ,
    [cmBit03] [bit] NULL ,
    [cmBit04] [bit] NULL ,
    [cmBit05] [bit] NULL ,
    [cmBit06] [bit] NULL ,
    [cmBit07] [bit] NULL ,
    [cmBit08] [bit] NULL ,
    [cmBit09] [bit] NULL ,
    [cmBit10] [bit] NULL ,
    [cmBit11] [bit] NULL ,
    [cmSmallint01] [smallint] NULL ,
    [cmSmallint02] [smallint] NULL ,
    [cmSmallint03] [smallint] NULL ,
    [cmSmallint04] [smallint] NULL ,
    [cmSmallint05] [smallint] NULL ,
    [cmSmallint06] [smallint] NULL ,
    [cmSmallint07] [smallint] NULL ,
    [cmSmallint08] [smallint] NULL ,
    [cmTinyint01] [tinyint] NULL ,
    [SooOrder] [numeric](5, 3) NULL ,
    [SooPrecedent] [numeric](5, 3) NULL ,
    [SooDelay] [numeric](5, 3) NULL ,
    [SooNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [cmUserNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [cmCreatedOn] [smalldatetime] NOT NULL ,
    [cmCreatedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [cmEditedOn] [smalldatetime] NOT NULL ,
    [cmEditedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
    [cmrowversion] [timestamp] NOT NULL ,
    CONSTRAINT [PK_tbl040Cmpt] PRIMARY KEY CLUSTERED
    (
        [pkComponentId]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [FK_tbl040Cmpt_lkp202ComponentType] FOREIGN KEY
    (
        [fkComponentType]
    ) REFERENCES [lkp202ComponentType] (
        [pkComponentTypeId]
    ),
    CONSTRAINT [FK_tbl040Cmpt_tbl030Devi] FOREIGN KEY
    (
        [fkDevice]
    ) REFERENCES [tbl030Devi] (
        [pkDeviceId]
    ) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
//

16 Dec 2005 3:44 AM

Uri Dimant

Hi
Uh, please lookup 'deleted', 'inserted' virtual tables in the BOL

Show quote

"dbuchanan" <dbuchana***@hotmail.com> wrote in message
news:1134697526.342459.131070@o13g2000cwo.googlegroups.com...
> Hello,
>
> I need some help with this trigger.
>
> I am a newbie at Triggers as you will see. I have had difficulty with
> some of the details of the concept. My questions are within the text of
> my attempt at the trigger.
>
> Here is how it is to work:
> When the column "Order" is changed for a record in "tbl040Cmpt" I want
> to change the column "Order" in other records in the same table that
> have a kind-of association; The one record I will call the 'parent' the
> other records I will call the 'children' The 'children' have the Id of
> the 'parent' contained in another column named 'cmVarchar06'. They are
> not really parents and children have a kind-f associaltion that mimic
> it.
>
> Here is my attempt:
> \\
> CREATE TRIGGER UpdateOrderForSensorChildrenWhenValveIsChanged
> ON tbl040Cmpt AFTER UPDATE
>
> DECLARE @Order_old numeric(5,3),
> @Order_New numeric(5,3),
> @cmVarchar06 varchar(50),
> @cmBit11 bit, -- Valve Action (when coming from the triggering record)
> -- @fkComponentType int
>
> -- Get the value of the changed field
> SELECT @Order_old = Order FROM tbl040Cmpt --<<How do I get this value
> if the column has already just been updated by a previous stored
> procedure?
> SELECT @Order_new = Order FROM tbl040Cmpt --<<Is this how I get the
> new value?
> SELECT @cmVarchar06 = fkDevice FROM tbl040Cmpt -- The record that
> fires the trigger is an associated 'parent' of its sensors
> SELECT @cmBit11 = cmBit11 FROM tbl040Cmpt -- The value of this column
> indicates whether the valve is advance or return (* does advance of
> valve mean 'advance' of cyln, * does advance of valve mean 'on' of Air)
> -- SELECT @fkComponentType = fkComponentType FROM tbl040Cmpt -- This
> will always be 1 for Valve
>
> -- when these conditions are met
> Where fkComponentType = 1 -- Valve
> and @Order_old <> @ Order_new -- does the trigger evaluate all updates
> to records in this table even though the order column is not changed?
>
> -- Now copy the Order value to the qualifying records
>
> IF @cmBit11 = 1 THEN -- ValveActionIsAdvance = TRUE (air service or
> cyln sensor)
>
> -- ***** Cylinder sensor update *** Advance ****
> Update tbl040Cmpt
> Set Order = @Order
>
> Where fkComponentType = 7 -- Motion Sensor ~ (a cylinder sensor)
> and cmVarchar06 = @cmVarchar06 -- a motion sensor associated with the
> cylinder
> and cmBit11 = 1 -- Sensor Action is On (for records filtered by the
> update criteria)
> and (cmbit05 = 1 -- Adv
> or cmbit04 = 1) -- Almost Adv
> -- **************************************
>
> -- ***** Air Service sensor update *** On *******
> Update tbl040Cmpt
> Set Order = @Order
>
> Where fkComponentType = 3 -- Non-Motion Sensor (a air service sensor)
> and cmVarchar06 = @cmVarchar06 -- from the same parent
> and cmBit11 = 1 -- Non-Motion Sensor is 'On'
> -- **************************************
>
> ELSE -- cmBit11 = 0 ~ ValveActionIsAdvance = FALSE (air service or cyln
> sensor)
>
> -- ***** Cylinder sensor update *** Return ******
> Update tbl040Cmpt
> Set Order = @Order
>
> Where fkComponentType = 7 -- Motion Sensor ~ (a cylinder sensor)
> and cmVarchar06 = @cmVarchar06 -- a motion sensor associated with the
> cylinder
> and cmBit11 = 1 -- Sensor Action is On (for records filtered by the
> update criteria)
> and cmbit01 = 1 -- Rtn
> or cmbit02 = 1 -- Almost Rtn
> -- **************************************
>
> -- ***** Air Service sensor update *** Off *******
> Update tbl040Cmpt
> Set Order = @Order
>
> Where fkComponentType = 3 -- Non-Motion Sensor (a air service sensor)
> and cmVarchar06 = @cmVarchar06 -- from the same parent
> and cmBit11 = 0 -- Non-Motion Sensor is 'Off'
> -- **************************************
>
> END IF
> //
>
>
> I have not tried it because the are a couple of basic elements missing.
> Please direct me.
>
>
> Thank you,
> dbuchanan
>
>
> Extra information;
>
> Here is my table:
> \\
> CREATE TABLE [tbl040Cmpt] (
> [pkComponentId] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL CONSTRAINT [DF_tbl040Cmpt_pkComponentId] DEFAULT (newid()),
> [fkDevice] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [fkComponentType] [int] NOT NULL ,
> [ComponentDescription] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [QuanSharingConfiguration] [tinyint] NOT NULL ,
> [QuanConfigured] [tinyint] NOT NULL CONSTRAINT
> [DF_tbl040Cmpt_QuanConfigured] DEFAULT (0),
> [QuanActuations] [tinyint] NOT NULL CONSTRAINT
> [DF_tbl040Cmpt_QuanActuations] DEFAULT (1),
> [cmVarchar01] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [cmVarchar02] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [cmVarchar03] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [cmVarchar04] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [cmVarchar05] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [cmVarchar06] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [cmNumeric01] [numeric](5, 3) NULL ,
> [cmNumeric02] [numeric](5, 3) NULL ,
> [cmNumeric03] [numeric](5, 3) NULL ,
> [cmNumeric04] [numeric](5, 3) NULL ,
> [cmNumeric05] [numeric](5, 3) NULL ,
> [cmNumeric06] [numeric](5, 3) NULL ,
> [cmNumeric07] [numeric](5, 3) NULL ,
> [cmBit01] [bit] NULL ,
> [cmBit02] [bit] NULL ,
> [cmBit03] [bit] NULL ,
> [cmBit04] [bit] NULL ,
> [cmBit05] [bit] NULL ,
> [cmBit06] [bit] NULL ,
> [cmBit07] [bit] NULL ,
> [cmBit08] [bit] NULL ,
> [cmBit09] [bit] NULL ,
> [cmBit10] [bit] NULL ,
> [cmBit11] [bit] NULL ,
> [cmSmallint01] [smallint] NULL ,
> [cmSmallint02] [smallint] NULL ,
> [cmSmallint03] [smallint] NULL ,
> [cmSmallint04] [smallint] NULL ,
> [cmSmallint05] [smallint] NULL ,
> [cmSmallint06] [smallint] NULL ,
> [cmSmallint07] [smallint] NULL ,
> [cmSmallint08] [smallint] NULL ,
> [cmTinyint01] [tinyint] NULL ,
> [SooOrder] [numeric](5, 3) NULL ,
> [SooPrecedent] [numeric](5, 3) NULL ,
> [SooDelay] [numeric](5, 3) NULL ,
> [SooNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [cmUserNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cmCreatedOn] [smalldatetime] NOT NULL ,
> [cmCreatedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cmEditedOn] [smalldatetime] NOT NULL ,
> [cmEditedBy] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cmrowversion] [timestamp] NOT NULL ,
> CONSTRAINT [PK_tbl040Cmpt] PRIMARY KEY CLUSTERED
> (
> [pkComponentId]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_tbl040Cmpt_lkp202ComponentType] FOREIGN KEY
> (
> [fkComponentType]
> ) REFERENCES [lkp202ComponentType] (
> [pkComponentTypeId]
> ),
> CONSTRAINT [FK_tbl040Cmpt_tbl030Devi] FOREIGN KEY
> (
> [fkDevice]
> ) REFERENCES [tbl030Devi] (
> [pkDeviceId]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
> //
>

16 Dec 2005 3:31 PM

dbuchanan

Hello Uri,

I wasn't sure what you were getting at but the lookup lead me to
'virtual tables' or 'Views'. By creating two views I have greatly
simplified my Trigger.

I still have a few questions.

I understand that this trigger will be run (evaluated) on every Update
to "tbl040Cmpt" becasue of this statement:
    ON tbl040Cmpt AFTER UPDATE

Should I let this run every time or should I set a condition? The
condition would be if the "Order" column were updated.

If I need a condition would I compare its old value to its new value?
If I compare old to new value how do I get the old value if the Trigger
runs *after* the preceding update has already run?

Or am I on completely the wrong track?

Here is my revised trigger:
\\
CREATE TRIGGER UpdateOrderForSensorChildrenWhenValveIsChanged
ON tbl040Cmpt AFTER UPDATE

DECLARE
    @Order_old numeric(5,3),
    @Order_New numeric(5,3),
    @cmVarchar06 varchar(50),

-- Get the value of the changed field
        SELECT @Order_old = Order FROM tbl040Cmpt -- Question #1
        SELECT @Order_new = Order FROM tbl040Cmpt -- Question #2
        SELECT @cmVarchar06 = fkDevice FROM tbl040Cmpt

-- when these conditions are met
Where   fkComponentType = 1 -- Valve
        and @Order_old <> @ Order_new

-- [1] Copy the 'Order' value to "Advance" Motion sensors for cylinders

Update View7Msen
        Set Order = @Order
Where
        cmVarchar06 = @cmVarchar06 -- a motion sensor associated with
the cylinder
        and (cmbit05 = 1 or cmbit04 = 1) -- Advanced or Almost Advanced

-- [2] Copy the 'Order' value to "Return" Motion sensors for cylinders
Update View7Msen
        Set Order = @Order

Where   cmVarchar06 = @cmVarchar06 -- a motion sensor associated with
the cylinder
        and (cmbit01 = 1 or cmbit02 = 1) -- Returned or Almost Returned

-- [3] Copy the 'Order' value to "On" Non-Motion Air Service sensors
Update View3Nsen
        Set Order = @Order

Where   cmVarchar06 = @cmVarchar06 -- from the same parent
        and cmBit11 = 1 -- Non-Motion Sensor is "On"

-- [4] Copy the 'Order' Value to "Off" Non-Motion Air Service sensors
Update View3Nsen
        Set Order = @Order

Where   cmVarchar06 = @cmVarchar06 -- from the same parent
        and cmBit11 = 0 -- Non-Motion Sensor is "Off"
//

18 Dec 2005 12:07 AM

Hugo Kornelis

On 16 Dec 2005 07:31:33 -0800, dbuchanan wrote:

Show quote

>Hello Uri,
>
>I wasn't sure what you were getting at but the lookup lead me to
>'virtual tables' or 'Views'. By creating two views I have greatly
>simplified my Trigger.
>
>I still have a few questions.
>
>I understand that this trigger will be run (evaluated) on every Update
>to "tbl040Cmpt" becasue of this statement:
> ON tbl040Cmpt AFTER UPDATE
>
>Should I let this run every time or should I set a condition? The
>condition would be if the "Order" column were updated.
>
>If I need a condition would I compare its old value to its new value?
>If I compare old to new value how do I get the old value if the Trigger
>runs *after* the preceding update has already run?
>
>Or am I on completely the wrong track?

Hi dbuchanan,

Yes, you are on the wrong track.

You can't get the old and the new value of the Order column from the
table. You get those from the special pseudotables inserted and deleted,
that you can use only in the body of a trigger.

But what's worse: a tigger doesn't fire once per row updated, but once
per statement executed. The inserted and deleted pseudo-tables will hold
0, 1, or many rows, depending on the number of rows affected by the
SELECT statement. Your trigger should be able to handle zero-row and
multi-row updates. Your current trigger doesn't.

There are two ways to test if a column has changed in a trigger. The
UPDATE(column_name) function gives a first estimate: it is true if the
column name was in the SET clause of the UPDATE statement. It says that
the column *might* have been updated. Of course, if the new value is
equal to the old value, you might not consider it a real update. To
check that, you need to compare values in the inserted and deleted
tables.

Based on my (hopefully correct) interpretation of your requirements from
reading your current code, I think you can use something like this:

CREATE TRIGGER UpdateOrderForSensorChildrenWhenValveIsChanged
ON tbl040Cmpt AFTER UPDATE
AS
BEGIN
-- Performance - quit immediately if no rows affected
IF @@ROWCOUNT = 0 RETURN
-- Performance - quit immediately if Order column not affected
IF NOT UPDATE(Order) RETURN
-- Safety - inserted and deleted rows can only be matched on the
--   primary key, so don't allow it to change!
IF UPDATE(pkComponentId)
BEGIN
    RAISERROR('Don''t change pkComponentId and Order at the same time',
16, 1)
    ROLLBACK TRANSACTION
    RETURN
END
-- Make required modifications for all valves with changed order:
-- [1] Copy 'Order' value to "Advance" Motion sensors for cylinders
-- [2] Copy 'Order' value to "Return" Motion sensors for cylinders
UPDATE     View7Msen
SET        Order = i.Order
FROM       View7Msen AS v
INNER JOIN tbl040Cmpt AS t
        ON   v.cmVarchar06 = t.fkDevice
INNER JOIN deleted AS d
        ON   d.Order = v.Order
INNER JOIN inserted AS i
        ON   i.pkComponentId = d.pkComponentId
WHERE      t.fkComponentType = 1
AND        d.Order <> i.Order
AND ( (   v.cmbit05 = 1
          OR v.cmbit04 = 1 ) -- Advanced or Almost Advanced
      OR (   v.cmbit01 = 1
          OR v.cmbit02 = 1 )) -- Returned or Almost Returned

-- [3] Copy 'Order' value to "On" Non-Motion Air Service sensors
-- [4] Copy 'Order' Value to "Off" Non-Motion Air Service sensors
UPDATE     View3Nsen
SET        Order = i.Order
FROM       View7Msen AS v
INNER JOIN tbl040Cmpt AS t
        ON   v.cmVarchar06 = t.fkDevice
INNER JOIN deleted AS d
        ON   d.Order = v.Order
INNER JOIN inserted AS i
        ON   i.pkComponentId = d.pkComponentId
WHERE      t.fkComponentType = 1
AND        d.Order <> i.Order
AND        v.cmBit11 IN (1, 0) -- Non-Motion Sensor is "On" or "Off"

END

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

18 Dec 2005 1:33 AM

dbuchanan

Hugo,

I am having difficulty understanding the Trigger. Triggers are
completely new to me. I just found "Using the inserted and deleted
Tables" in the BOL and I will study it. Is there any other documention
you might suggest.

Here's one question; In the line SET just after UPDATE...

SET Order = i.Order

Where was the "i" defined?

Thank you,
dbuchanan

18 Dec 2005 10:15 PM

Hugo Kornelis

On 17 Dec 2005 17:33:04 -0800, dbuchanan wrote:

>Hugo,
>
>I am having difficulty understanding the Trigger. Triggers are
>completely new to me. I just found "Using the inserted and deleted
>Tables" in the BOL and I will study it. Is there any other documention
>you might suggest.

Hi dbuchanan,

The archives of this group are a great place to find good (and,
unfortunately, bad) examples. If you stick to examples posted by MVPs
(or by me <g>) and read any follow-up comments, you'll learn a lot.

I recommend BOL as a source for examples for many subjects, but NOT for
triggers. I don't know if it's improved in the SQL Server 2005 version
of BOL, but the examples in the 2000 version are hideous.

>
>Here's one question; In the line SET just after UPDATE...
>
>SET Order = i.Order
>
>Where was the "i" defined?

It was defined here:

INNER JOIN inserted AS i

The i is a table alias. I usually choose to use aliasses for all tables
in my query because it saves typing and yield code that is (IMO) easier
to read.

Without the alias, the join would have read

INNER JOIN inserted

and the SET would hafve read

SET Order = inserted.Order

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

19 Dec 2005 10:43 AM

dbuchanan

Hugo,

Thank you for the advice. I'll do that.

I know that the "i" is an alias, but don't you somewhere, *before*
using the alias "i", have to say "inserted as i"? That does not occur
before "i.order".

dbuchanan

19 Dec 2005 11:18 AM

David Portas

dbuchanan wrote:

>
> I know that the "i" is an alias, but don't you somewhere, *before*
> using the alias "i", have to say "inserted as i"? That does not occur
> before "i.order".
>
> dbuchanan

No. In a SQL statement the table aliases are always defined in the FROM
or JOIN clause or following a derived table definition. FROM / JOIN
comes after the select list or set list in a SELECT or UPDATE statement
but it's still legal to refer to the alias in the select or set list.

--
David Portas
SQL Server MVP
--