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,
OwenCREATE 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.3320@.TK2MSFTNGP12.phx.gbl...
> 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
>|||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.examnotes.net ***|||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)|||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
>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)|||You're right Hugo,
Thanks,
T.Berimi
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> a crit dans le message de
news: l5l781lt68ctijp3ov37rjv7cd0m5l1ifu@.4ax.com...
> On Thu, 12 May 2005 18:20:05 +0100, Berimi wrote:
>
> (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)
kbutterly wrote:
kbutterly wrote: