Newbie (to Triggers) Trigger Help....

microsoft.public.sqlserver.programming

12 May 2005 4:11 PM

Owen Mortensen

Where can I find good information about how to write triggers? I'm using SQL
Server 2000.

Now, the task at hand: How can I write a trigger that reacts on both insert
and update to keep some fields in two similar (but not the same) tables in
sync. (I need to both insert and update the 2nd table).

Here's some sample DBs (is there a better way to describe the database?):
CREATE TABLE [dbo][tblUserMain](
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userPassword] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[dtLastActive] [datetime] NOT NULL ,
[GUID] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblUserExt] (
[CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userRefBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userProfession] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userBulkMail] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userSubspecialties] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[userTechnologies] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[dtRegDate] [datetime] NOT NULL ,
[userPopQuiz] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userAdvertise] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userBulkMailHtml] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userTitle] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[orgStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[orgStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userHomeTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[userWorkTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userDegree] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[needsUpdate] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SavedSearches] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[userGroups] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userPersonalize] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[userRememberMe] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[middleName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[needsUpdateMessage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[pwdQuestion] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pwdAnswer] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[homePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mailerEmailInvalid] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I need a trigger so that when the lastName, firstName and bill* fields
change (or insert) in the tblUserExt table they get updated (or inserted)
into the tblUserMain table. These triggers would be temporary until the
tblUserExt table is made obsolete....

Pseudocode----
On Insert,
Update the name and bill* fields in the tblUserMain table (the row will
already exist)
On Update,
Update the name and bill* fields (if they changed) in the tblUserMain
table

TIA,
Owen

12 May 2005 5:20 PM

Berimi

CREATE TRIGGER Triger_name ON tblUserExt
FOR INSERT
AS
DECLARE @name NVARCHAR(50)
DECLARE @billname NVARCHAR(50)

SELECT @name=name FROM INSERTED
SELECT @billname=billname FROM INSERTED

INSERT INTO tblUserMain (name,billname) VALUES (@name,@billname)
-- or INSERT INTO tblUserMain (the required fields) SELECT
ins.the_same_required_ fileds FROM INSERTED ins
------------------------------------------------------------
CREATE TRIGGER Triger_name ON tblUserExt
FOR UPDATE
AS
DECLARE @name NVARCHAR(50)
DECLARE @billname NVARCHAR(50)
DECLARE @Oldname NVARCHAR(50) --before updated
DECLARE @Oldbillname NVARCHAR(50)--before updated

SELECT @name=name FROM INSERTED
SELECT @billname=billname FROM INSERTED

SELECT @Oldname=name FROM tblUserExt
SELECT @Oldbillname=billname FROM tblUserExt

UPDATE tblUserMain
SET name = @name,
billname = @billname
WHERE name = @name AND billname = @Oldbillname

Note that @name has to be changed to all the fields like
firstname,lastname,..., same thong for billname.

"Owen Mortensen" <ojm.NO_SPAM@acm.org> a écrit dans le message de news:

elI550wVFHA.3***@TK2MSFTNGP12.phx.gbl...
Show quote

> Where can I find good information about how to write triggers? I'm using
> SQL Server 2000.
>
> Now, the task at hand: How can I write a trigger that reacts on both
> insert and update to keep some fields in two similar (but not the same)
> tables in sync. (I need to both insert and update the 2nd table).
>
> Here's some sample DBs (is there a better way to describe the database?):
> CREATE TABLE [dbo][tblUserMain](
> [userID] [int] IDENTITY (1, 1) NOT NULL ,
> [CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userPassword] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [dtLastActive] [datetime] NOT NULL ,
> [GUID] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [dbo].[tblUserExt] (
> [CN] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [firstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [lastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userRefBy] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userProfession] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userBulkMail] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userSubspecialties] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [userTechnologies] [nvarchar] (750) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [dtRegDate] [datetime] NOT NULL ,
> [userPopQuiz] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [orgPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgType] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [userAdvertise] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userBulkMailHtml] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userTitle] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [orgStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [orgStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userHomeTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [userWorkTelephone] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [billCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billCountry] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [billPostal] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [billProvince] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [billStreet2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userDegree] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [needsUpdate] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SavedSearches] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userGroups] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [userPersonalize] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [userRememberMe] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [middleName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [needsUpdateMessage] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [pwdQuestion] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pwdAnswer] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [homePage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [language] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [mailerEmailInvalid] [int] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
> I need a trigger so that when the lastName, firstName and bill* fields
> change (or insert) in the tblUserExt table they get updated (or inserted)
> into the tblUserMain table. These triggers would be temporary until the
> tblUserExt table is made obsolete....
>
> Pseudocode----
> On Insert,
> Update the name and bill* fields in the tblUserMain table (the row will
> already exist)
> On Update,
> Update the name and bill* fields (if they changed) in the tblUserMain
> table
>
> TIA,
> Owen
>

12 May 2005 5:30 PM

Carl Federl

Regarding: "Here's some sample DBs (is there a better way to describe
the database?)" - What you have provided is fine, but any constraints
(primary key, unique and foreign keys) are also needed. If you
generated this thru Query Analyzer, you can set the options for DDL
generation.
Use the Tools-->Options menu item and then the scripts tab.

Here is part of an update trigger to syncronize the First and Last Name.
This assumes that, in both tables, the column named CN is unique and has
the same value. I have included the comparison logic for First and Last
Name allowing nulls.

CREATE TRIGGER tblUserExt_tua -- Trigger Update After
    ON tblUserExt FOR UPDATE
AS
set nocount on
set xact_abort on
-- Check if any rows affected by the command
declare    @Rows    integer
SELECT @Rows = count(*) from inserted
IF @rows = 0 return

UPDATE dbo.tblUserMain
SET    firstName = inserted.firstName
,    lastName = inserted.lastName
FROM     inserted
WHERE    dbo.tblUserMain.CN = inserted.CN
AND    (    inserted.firstName <> dbo.tblUserMain.firstName
    OR    (    inserted.firstName IS NULL
        and     dbo.tblUserMain.firstName IS NOT NULL
        )
    OR    (    inserted.firstName IS NOT NULL
        and     dbo.tblUserMain.firstName IS NULL
        )
    OR     inserted.lastName <> dbo.tblUserMain.lastName
    OR    (    inserted.lastName IS NOT NULL
        and     dbo.tblUserMain.firstName IS NULL
        )
    OR    (    inserted.lastName IS NULL
        and     dbo.tblUserMain.firstName IS NOT NULL
        )
    )
)
go

*** Sent via Developersdex http://www.developersdex.com ***

12 May 2005 10:13 PM

Hugo Kornelis

On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:

>CREATE TRIGGER Triger_name ON tblUserExt
>FOR INSERT
>AS
>DECLARE @name NVARCHAR(50)
>DECLARE @billname NVARCHAR(50)
>
>SELECT @name=name FROM INSERTED
>SELECT @billname=billname FROM INSERTED
(snip)

Hi Berimi,

This trigger (and the trigger you wrote for UPDATE) will fail as soon as
an insert or update statement is executed that affects more than one
row. And it will fail even worse when a statement is executed that
affects no rows.

Always write triggers that can handle multi-row and zero-row operations!

Best, Hugo

--

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

12 May 2005 11:30 PM

Berimi

You're right Hugo,

Thanks,

T.Berimi

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> a écrit dans le message de

news: l5l781lt68ctijp3ov37rjv7cd0m5l1***@4ax.com...
Show quote

> On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:
>
>>CREATE TRIGGER Triger_name ON tblUserExt
>>FOR INSERT
>>AS
>>DECLARE @name NVARCHAR(50)
>>DECLARE @billname NVARCHAR(50)
>>
>>SELECT @name=name FROM INSERTED
>>SELECT @billname=billname FROM INSERTED
> (snip)
>
> Hi Berimi,
>
> This trigger (and the trigger you wrote for UPDATE) will fail as soon as
> an insert or update statement is executed that affects more than one
> row. And it will fail even worse when a statement is executed that
> affects no rows.
>
> Always write triggers that can handle multi-row and zero-row operations!
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

12 May 2005 10:25 PM

Hugo Kornelis

On Thu, 12 May 2005 10:30:54 -0700, Carl Federl wrote:

(snip)

>CREATE TRIGGER tblUserExt_tua -- Trigger Update After
> ON tblUserExt FOR UPDATE

If I understand the OP's requirements correct, one trigger can handle
both inserts and updates:

ON tblUserExt FOR INSERT, UPDATE

>-- Check if any rows affected by the command
>declare @Rows integer
>SELECT @Rows = count(*) from inserted
>IF @rows = 0 return

This will waste unnecessary time when 1000s of rows were affected. Use
EXISTS instead:

IF NOT EXISTS (SELECT * FROM inserted) RETURN

Or, better yet, use @@ROWCOUNT (at the start of a trigger, this holds
the number of rows affected by the stmt that fired the trigger):

IF @@ROWCOUNT = 0 RETURN

Show quote

>UPDATE dbo.tblUserMain
>SET    firstName = inserted.firstName
>,    lastName = inserted.lastName
>FROM     inserted
>WHERE    dbo.tblUserMain.CN = inserted.CN
>AND    (    inserted.firstName <> dbo.tblUserMain.firstName
>    OR    (    inserted.firstName IS NULL
>        and     dbo.tblUserMain.firstName IS NOT NULL
>        )
>    OR    (    inserted.firstName IS NOT NULL
>        and     dbo.tblUserMain.firstName IS NULL
>        )
>    OR     inserted.lastName <> dbo.tblUserMain.lastName
>    OR    (    inserted.lastName IS NOT NULL
>        and     dbo.tblUserMain.firstName IS NULL
>        )
>    OR    (    inserted.lastName IS NULL
>        and     dbo.tblUserMain.firstName IS NOT NULL
>        )
>    )
>)

The test for changed data in nullable columns can be done in a shorter
form. It's less intuitive on first sight, but it saves you lots of lines
of code (important if this has to grow to accomodate 50-odd columns!),
and it's easy once you get used to it:

UPDATE     u
SET        firstName = i.firstName
         , lastName = i.lastName
FROM        dbo.tblUserMain AS u
INNER JOIN inserted AS i
      ON   i.CN = u.CN
WHERE (    NULLIF (i.firstName, u.firstName) IS NOT NULL
    OR NULLIF (u.firstName, i.firstName) IS NOT NULL)
AND   (    NULLIF (i.lastName, u.lastName) IS NOT NULL
    OR NULLIF (u.lastName, i.lastName) IS NOT NULL)

Of course, the check for unchanged data could be left out completely.
Without it, finding the rows to operate on would be much quicker, at the
cost of possibly updating the values in some rows to the values they
already had. To prevent updating when no first or last names have been
changed, you can add (before the UPDATE statement):

IF UPDATE(FirstName) OR UPDATE(LastName)

Best, Hugo

--

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