INSERTED table and triggers

Hi. I was dealing with triggers when a doubt came in mind.

While I can understand that the DELETED and UPDATED tables can contain more rows that have been affected by the DELETE or the UPDATE statment, the INSERTED table that I read in a "FOR INSERT" trigger has just 1 row or can have more rows?

Thanks.

many rows. Number of rows depended on how many rows get deleted / updated / inserted|||Image the query

INSERT INTO SomeTable
SELECT SomeCOlumn From ManyRowTable

That will bring up more than one row. bew also aware that the trigger is fired upon DML statement not per row, this means that a query like

INSERT INTO SomeTable
SELECT SomeColumn From SomeTable2 Where 1 = 0

also brings the trigger to fire.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

inserted / deleted tables for triggers

Hi i was hoping someone could help me. If i have the following trigger
defined:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0)
FROM inserted
WHERE mytable.userid = inserted.userid
Iperform the following:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
lets take for example the row pertaining to userid = 1234 within mytable to
be:
userid field1 field2 field3
1234 0 1 2
What is the state of the inserted table when the trigger is fired? Does the
inserted table do the following:
1) copy into itself the row from mytable pertaining to userid = 1234
2) modify this copied row to reflect field1 = 1
so inserted looks like this:
userid field1 field2 field3
1234 1 1 2
OR
1) creates a row within itself with field1 = 1, and all the other fields set
to NULL?
so inserted looks like this:
userid field1 field2 field3
NULL 1 NULL NULL
Ay help most appreciated. I think i am slightly with the state of
the inserted/deleted tables when triggers are invovled.
Cheers,
peterAn UPDATE with a trigger is performed as a DELETE followed by an INSERT. So
the DELETED table will contain the *before* data records and the INSERTED
table will contain the *after* data records.
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
> Hi i was hoping someone could help me. If i have the following trigger
> defined:
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0)
> FROM inserted
> WHERE mytable.userid = inserted.userid
>
> Iperform the following:
>
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
>
> lets take for example the row pertaining to userid = 1234 within mytable
> to be:
> userid field1 field2 field3
> 1234 0 1 2
> --
> What is the state of the inserted table when the trigger is fired? Does
> the inserted table do the following:
> 1) copy into itself the row from mytable pertaining to userid = 1234
> 2) modify this copied row to reflect field1 = 1
> so inserted looks like this:
> userid field1 field2 field3
> 1234 1 1 2
> OR
> 1) creates a row within itself with field1 = 1, and all the other fields
> set to NULL?
> so inserted looks like this:
> userid field1 field2 field3
> NULL 1 NULL NULL
>
> Ay help most appreciated. I think i am slightly with the state of
> the inserted/deleted tables when triggers are invovled.
> Cheers,
> peter
>|||thanks, so an update removes the relevant row(s) from the trigger table and
sticks them into the deleted table; then inserts the new modified row(s)
into both the trigger table and the inserted table.
thanks for the clarification.
cheers, peter

> An UPDATE with a trigger is performed as a DELETE followed by an INSERT.
> So the DELETED table will contain the *before* data records and the
> INSERTED table will contain the *after* data records.
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:OlU7ioz0FHA.2428@.tk2msftngp13.phx.gbl...
>

Insert Triggers

I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?Within the trigger, you can access a view called INSERTED that shows only the rows that are being inserted by the statement that launched the trigger. You can use the INSERTED view (probably via a JOIN) to limit the number of rows you are affecting in your underlying table.

-PatP|||my telepathic usb port is clogged...can you post the trigger...

probably take us a few minutes...

DDL would be nice as well

and pat's correct(what again? say it ain't so...)|||CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select tblGroup.Id as DataRecordId,
1 as TaskNum,
"Ready" as Status,
tblUsers.Id as UserId,
getdate() as StartDateTime
from tblGroup, tblUsers, tblVendors where (tblGroup.I_Field3=tblVendors.OdissVendorId)
And (tblGroup.I_Field6 Is Null OR tblGroup.I_Field6='0')
And (tblUsers.WFID=1)

..a little complex. the check for tblGroup.I_Field6 is necessitated because all records are being checked - this where clause could be stripped off if only new records were being checked.|||Something like this would do it:

CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
if exists (select 1 from inserted)
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select i.Id, 1, 'Ready', u.Id, getdate()
from inserted i
inner join tblVendors v
on i.I_Field3=v.OdissVendorId
inner join tblUsers u
on (u.WFID=1)|||thanx..will try this.

Insert Trigger Help

Hello,

I'm new with triggers and I can not find any good example on how to
do the following:

I have two tables WO and PM with the following fields:

WO.WONUM, VARCHAR(10)
WO.PMNUM, VARCHAR(10)
WO.PROBLEMCODE, VARCHAR(8)
WO.LABORGROUP, VARCHAR(8)

PM.PMNUM, VARCHAR(10)
PM.PROBLEMCODE, VARCHAR(8)
PM.LABORGROUP, VARCHAR(8)

When creating a new record on WO I need to create an INSERT TRIGGER
that will pass the data below from PM to WO when WO.PMNUM = PM.PMNUM

PM.PROBLEMCODE to WO. PROBLEMCODE and
PM.LABORGROUP to WO. LABORGROUP

Could anybody please show me how to do this or point me to the right
direction, any help will be greatly appreciated.

Thanks!

Martin"Martin" <martin.wunder@.wsidc.com> wrote in message
news:1104861858.065373.23600@.c13g2000cwb.googlegro ups.com...
> Hello,
> I'm new with triggers and I can not find any good example on how to
> do the following:
> I have two tables WO and PM with the following fields:
> WO.WONUM, VARCHAR(10)
> WO.PMNUM, VARCHAR(10)
> WO.PROBLEMCODE, VARCHAR(8)
> WO.LABORGROUP, VARCHAR(8)
> PM.PMNUM, VARCHAR(10)
> PM.PROBLEMCODE, VARCHAR(8)
> PM.LABORGROUP, VARCHAR(8)
> When creating a new record on WO I need to create an INSERT TRIGGER
> that will pass the data below from PM to WO when WO.PMNUM = PM.PMNUM
> PM.PROBLEMCODE to WO. PROBLEMCODE and
> PM.LABORGROUP to WO. LABORGROUP
> Could anybody please show me how to do this or point me to the right
> direction, any help will be greatly appreciated.
> Thanks!
> Martin

I don't really understand your description - are you saying that when you
insert a row into WO you want to update PROBLEMCODE and LABORGROUP with
corresponding values from the PM table, joined on the PMNUM column?

In future, please post table structure as DDL, ie. CREATE TABLE statements,
so it's clear what your keys and constraints are, along with INSERT
statements for sample data - this is much clearer than a description.

See below for sample code - it may be incorrect, but hopefully it will get
you started, at least.

Simon

create trigger dbo.ITR_WO
on dbo.WO after insert
as
begin
if @.@.rowcount = 0
return

update
dbo.WO
set
LABORGROUP=p.LABORGROUP,
PROBLEMCODE=p.PROBLEMCODE
from
dbo.PM p
join dbo.WO w
on i.PMNUM = WO.PMNUM
end|||You need to create an Insert Trigger in the WO table to do an update
joining the PM table when the keys are the same. Let me know if you
need the syntex..!|||On 4 Jan 2005 10:04:18 -0800, Martin wrote:

(snip)
>Could anybody please show me how to do this or point me to the right
>direction, any help will be greatly appreciated.

Hi Martin,

Simon already gave you some code that might do what you want (but beware
of unexpected results if one row in PM is matched by more than one row in
WO), but I'd like to question the reason for what you want to do.

The table names WO and PM don't reveal anything about your business, of
course, so I might be wrong - but from the looks of it, your design is
violating third normal form. Are you sure that you wouldn't be better off
removing the problemcode and laborgroup from the WO table, and joining the
PM table in when you need to report these properties for a given WOnum?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Simon Hayes (sql@.hayes.ch) writes:
> create trigger dbo.ITR_WO
> on dbo.WO after insert
> as
> begin
> if @.@.rowcount = 0
> return
> update
> dbo.WO
> set
> LABORGROUP=p.LABORGROUP,
> PROBLEMCODE=p.PROBLEMCODE
> from
> dbo.PM p
> join dbo.WO w
> on i.PMNUM = WO.PMNUM
> end

The line

join dbo.WO w

ought to be

join inserted w

Simon knows this as well, but for Martin this call for an explanation.
"inserted" is a virtual table that holds the rows inserted by the
INSERT statement. Note that the trigger fires once per statement, and
the virttual table, thus can have many rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95D517178C9Yazorman@.127.0.0.1...
> Simon Hayes (sql@.hayes.ch) writes:
>> create trigger dbo.ITR_WO
>> on dbo.WO after insert
>> as
>> begin
>> if @.@.rowcount = 0
>> return
>>
>> update
>> dbo.WO
>> set
>> LABORGROUP=p.LABORGROUP,
>> PROBLEMCODE=p.PROBLEMCODE
>> from
>> dbo.PM p
>> join dbo.WO w
>> on i.PMNUM = WO.PMNUM
>> end
> The line
> join dbo.WO w
> ought to be
> join inserted w
> Simon knows this as well, but for Martin this call for an explanation.
> "inserted" is a virtual table that holds the rows inserted by the
> INSERT statement. Note that the trigger fires once per statement, and
> the virttual table, thus can have many rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Oops, my mistake - thanks for the correction, Erland.

Simon|||Hello Guys,

Thank you for all your feedback!.

Below is the trigger that I'm using and Hugo is right there are many
PMs on WOs so it updates all the WO that matches the PMNUM..

CREATE TRIGGER GENERATE_PM_WO ON [workorder]
FOR INSERT
AS
BEGIN
IF @.@.rowcount = 0
RETURN
UPDATE [workorder]
SET
woassignmntqueueid = P.PM2,
problemcode = P.PM1
FROM
[pm]AS P join inserted AS I
on P.PMNUM = I.PMNUM
END

Any way, here is now the situation, these fields, woassignmntqueueid
and problemcode are required (NOT NULL ALLOWED) on the workorder table;
so, this trigger never executes. What do I need to do, to update only
the current workorder passing pm.pm2 and pm.pm1 to woassignmntqueueid
and problemcode.

Any help will be appreciated.

Thanks!

Martin

Hugo Kornelis wrote:
> On 4 Jan 2005 10:04:18 -0800, Martin wrote:
> (snip)
> >Could anybody please show me how to do this or point me to the right
> >direction, any help will be greatly appreciated.
> Hi Martin,
> Simon already gave you some code that might do what you want (but
beware
> of unexpected results if one row in PM is matched by more than one
row in
> WO), but I'd like to question the reason for what you want to do.
> The table names WO and PM don't reveal anything about your business,
of
> course, so I might be wrong - but from the looks of it, your design
is
> violating third normal form. Are you sure that you wouldn't be better
off
> removing the problemcode and laborgroup from the WO table, and
joining the
> PM table in when you need to report these properties for a given
WOnum?
> Best, Hugo|||On 20 Jan 2005 09:07:57 -0800, Martin wrote:

>Hello Guys,
>Thank you for all your feedback!.
>Below is the trigger that I'm using and Hugo is right there are many
>PMs on WOs so it updates all the WO that matches the PMNUM..
(sniup trigger code)

Hi Martin,

The code you posted is even worse: it will update ALL rows currently in
the workorder table. All these rows will have their woassignmntqueueid and
their problemcode set to PM2 and PM1 from a pm row that matches one of the
inserted rows - and if multiple rows are inserted, the trigger will just
choose one, semi-randomly.

I'm quite sure that this is not what you want - but I have no idea what
you do want.

>Any way, here is now the situation, these fields, woassignmntqueueid
>and problemcode are required (NOT NULL ALLOWED) on the workorder table;
>so, this trigger never executes.

This conclusion is wrong. Whether these rows allow NULLS or not has
nothing to do with the firing of this trigger. As soon as an INSERT
statement is run against the workorder table, this trigger *WILL* run, and
it *WILL* attempt to update *all* rows in workorder.

Of course, if the chosen value for either woassignmntqueueid or
problemcode happens to be NULL, the update will fail, causing an error in
the trigger and a rollback of the entire transaction (including the insert
statement that caused the trigger to fire). But the trigger DOES execute!

> What do I need to do, to update only
>the current workorder passing pm.pm2 and pm.pm1 to woassignmntqueueid
>and problemcode.

I'mm sorry, but your narrative is not sufficient to explain your exact
requirements. I suggest you post
* The structure of all related tables (as CREATE TABLE statements,
including datatypes, constraints and properties; irrelevant columns may be
omitted, especially if there are lots of them),
* Some illustrative sample data (as INSERT statements, so that I can use
cut and paste to run the code in Query Analyzer and recreate your sample
data on my test database),
* The required output, and
* A concise description of the business problem you're trying to solve.

Check out this site as well: http://www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,

Thanks for your help I really appreciate the time and effort that you
give to UNKNOWN people!

I have this program that helps us track the Work Orders request for
this site. Every day we use a WO Screen to enter the routine request
from our clients; all the data goes to the workorder Table and we have
the problemcode and the woassignmntqueueid fields set as required (Not
Null Allowed). This process works perfect we do not have any problems
with this daily process.

My Problem is that this program also have a PM (Preventive Maintenance)
screen that I have to use every February to generate all the PM work
orders for the whole year; these work orders or records are also
created in the workorder table and of course the data from the daily
work orders are different from the PM work orders and that is the
reason this program doesn't populate these to fields. Also in a few
occasion during the year I have to create new PM work order for
equipment that is added or replace on this site.

When I generate the PM for the site I have to do it after hours, I
configure the database and make those two fields to allow null values
then I run update queries to populate them, and then I reconfigure the
DB so these two fields are required again. The other problem is that if
we add or replace a piece of equipment and we need a PM work order
immediately, I can't happen.

That is the main reason I need to create this trigger.

My intention is when I'm on the PM screen and run the automate
routine to Generate or Create PM work order, this trigger will pass the
data from the PM table to the workorder table

Workorder.problemcode = pm.pm1
Workorder.woassignmntqueueid = pm.pm2

PM Table
Pmnun Description Pm1 Pm2
Hvac001 Monthly A/C Unit PM HVAC MAINT

Workorder Table
Wonum Description Pmnum problemcode woassignmntqueueid
1234567 Monthly A/C Unit PM Hvac001 HVAC MAINT

I have about 500 PM records and each can have many records on the
workorder table. The PMNUM field is the key field on the PM table and
a foreign key on the workorder table.

Below is the code to create and populate the workorder and PM table.

I really appreciate all your help.

Thanks!

Martin

create table workorder (
wonum varchar (10) not null ,
parent varchar (10) null ,
status varchar (8) not null ,
statusdate datetime not null ,
worktype varchar (5) null ,
leadcraft varchar (8) null ,
description varchar (50) null ,
eqnum varchar (8) null ,
location varchar (8) null ,
jpnum varchar (10) null ,
faildate datetime null ,
changeby varchar (18) null ,
changedate datetime null ,
estdur double precision not null ,
estlabhrs double precision not null ,
estmatcost decimal(10,2) not null ,
estlabcost decimal(10,2) not null ,
esttoolcost decimal(10,2) not null ,
pmnum varchar (8) null ,
actlabhrs double precision not null ,
actmatcost decimal(10,2) not null ,
actlabcost decimal(10,2) not null ,
acttoolcost decimal(10,2) not null ,
haschildren varchar (1) not null ,
outlabcost decimal(10,2) not null ,
outmatcost decimal(10,2) not null ,
outtoolcost decimal(10,2) not null ,
historyflag varchar (1) not null ,
contract varchar (8) null ,
wopriority integer null ,
wopm6 varchar (10) null ,
wopm7 decimal(15,2) null ,
targcompdate datetime null ,
targstartdate datetime null ,
woeq1 varchar (10) null ,
woeq2 varchar (10) null ,
woeq3 varchar (10) null ,
woeq4 varchar (10) null ,
woeq5 decimal(10,2) null ,
woeq6 datetime null ,
woeq7 decimal(15,2) null ,
woeq8 varchar (10) null ,
woeq9 varchar (10) null ,
woeq10 varchar (10) null ,
woeq11 varchar (10) null ,
woeq12 decimal(10,2) null ,
wo1 varchar (10) null ,
wo2 varchar (10) null ,
wo3 varchar (10) null ,
wo4 varchar (10) null ,
wo5 varchar (10) null ,
wo6 varchar (10) null ,
wo7 varchar (10) null ,
wo8 varchar (10) null ,
wo9 varchar (10) null ,
wo10 varchar (10) null ,
ldkey integer null ,
reportedby varchar (18) null ,
reportdate datetime null ,
phone varchar (20) null ,
problemcode varchar (8) not null ,
calendar varchar (8) null ,
interruptable varchar (1) null ,
downtime varchar (1) null ,
actstart datetime null ,
actfinish datetime null ,
schedstart datetime null ,
schedfinish datetime null ,
remdur double precision null ,
crewid varchar (8) null ,
supervisor varchar (8) null ,
woeq13 datetime null ,
woeq14 decimal(15,2) null ,
wopm1 varchar (10) null ,
wopm2 varchar (10) null ,
wopm3 varchar (10) null ,
wopm4 decimal(10,2) null ,
wopm5 varchar (10) null ,
wojp1 varchar (10) null ,
wojp2 varchar (10) null ,
wojp3 varchar (10) null ,
wojp4 decimal(10,2) null ,
wojp5 datetime null ,
wol1 varchar (10) null ,
wol2 varchar (10) null ,
wol3 decimal(10,2) null ,
wol4 datetime null ,
wolablnk varchar (8) null ,
respondby datetime null ,
eqlocpriority integer null ,
calcpriority integer null ,
chargestore varchar (1) not null ,
failurecode varchar (8) null ,
wolo1 varchar (10) null ,
wolo2 varchar (10) null ,
wolo3 varchar (10) null ,
wolo4 varchar (10) null ,
wolo5 varchar (10) null ,
wolo6 decimal(10,2) null ,
wolo7 datetime null ,
wolo8 decimal(15,2) null ,
wolo9 varchar (10) null ,
wolo10 integer null ,
glaccount varchar (20) null ,
estservcost decimal(10,2) not null ,
actservcost decimal(10,2) not null ,
disabled varchar (1) null ,
estatapprlabhrs double precision not null ,
estatapprlabcost decimal(10,2) not null ,
estatapprmatcost decimal(10,2) not null ,
estatapprtoolcost decimal(10,2) not null ,
estatapprservcost decimal(10,2) not null ,
wosequence integer null ,
hasfollowupwork varchar (1) not null ,
worts1 varchar (10) null ,
worts2 varchar (10) null ,
worts3 varchar (10) null ,
worts4 datetime null ,
worts5 decimal(15,2) null ,
wfid integer null ,
wfactive varchar (1) not null ,
sourcesysid varchar (10) null ,
ownersysid varchar (10) null ,
followupfromwonum varchar (10) null ,
pmduedate datetime null ,
pmextdate datetime null ,
pmnextduedate datetime null ,
viewwoasoper varchar (1) not null ,
woassignmntqueueid varchar (8) not null ,
worklocation varchar (8) null ,
wowq1 varchar (1) null ,
wowq2 varchar (1) null ,
wowq3 varchar (1) null ,
wojp6 varchar (10) null ,
wojp7 varchar (10) null ,
wojp8 varchar (10) null ,
wojp9 decimal(10,2) null ,
wojp10 datetime null ,
wo11 decimal(10,2) null ,
wo12 decimal(10,2) null ,
wo13 datetime null ,
wo14 datetime null ,
wo15 decimal(15,2) null ,
wo16 decimal(15,2) null ,
wo17 varchar (10) null ,
wo18 varchar (10) null ,
wo19 integer null ,
wo20 varchar (1) null ,
externalrefid varchar (10) null ,
apiseq varchar (50) null ,
interid varchar (50) null ,
migchangeid varchar (50) null ,
sendersysid varchar (50) null ,
expdone varchar (25) null ,
fincntrlid varchar (8) null ,
generatedforpo varchar (8) null ,
genforpolineid integer null ,
rowstamp timestamp
)
go

insert into workorder
( wonum, parent, status, statusdate, worktype, leadcraft, description,
eqnum, location,
jpnum, faildate, changeby, changedate, estdur, estlabhrs, estmatcost,
estlabcost, esttoolcost,
pmnum, actlabhrs, actmatcost, actlabcost, acttoolcost, haschildren,
outlabcost, outmatcost, outtoolcost,
historyflag, contract, wopriority, wopm6, wopm7, targcompdate,
targstartdate, woeq1, woeq2,
woeq3, woeq4, woeq5, woeq6, woeq7, woeq8, woeq9, woeq10, woeq11,
woeq12, wo1, wo2, wo3, wo4, wo5, wo6, wo7, wo8,
wo9, wo10, ldkey, reportedby, reportdate, phone, problemcode,
calendar, interruptable,
downtime, actstart, actfinish, schedstart, schedfinish, remdur,
crewid, supervisor, woeq13,
woeq14, wopm1, wopm2, wopm3, wopm4, wopm5, wojp1, wojp2, wojp3,
wojp4, wojp5, wol1, wol2, wol3, wol4, wolablnk, respondby,
eqlocpriority,
calcpriority, chargestore, failurecode, wolo1, wolo2, wolo3, wolo4,
wolo5, wolo6,
wolo7, wolo8, wolo9, wolo10, glaccount, estservcost, actservcost,
disabled, estatapprlabhrs,
estatapprlabcost, estatapprmatcost, estatapprtoolcost,
estatapprservcost, wosequence, hasfollowupwork, worts1, worts2, worts3,
worts4, worts5, wfid, wfactive, sourcesysid, ownersysid,
followupfromwonum, pmduedate, pmextdate,
pmnextduedate, viewwoasoper, woassignmntqueueid, worklocation, wowq1,
wowq2, wowq3, wojp6, wojp7,
wojp8, wojp9, wojp10, wo11, wo12, wo13, wo14, wo15, wo16,
wo17, wo18, wo19, wo20, externalrefid, apiseq, interid, migchangeid,
sendersysid,
expdone, fincntrlid, generatedforpo, genforpolineid)
values
( '7333', '7330', 'WAPPR', '1998-09-23 22:17:00', 'CP', NULL, 'Install
turntable', NULL, 'NEEDHAM',
NULL, NULL, 'MAXIMO', '1999-03-29 19:48:00', 16, 64, 0, 1172, 34,
NULL, 0, 0, 0, 0, 'N', 0, 0, 0,
'N', NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, 'MAXIMO', '1998-09-23 22:17:00', NULL, 'MAINT',
NULL, NULL,
NULL, NULL, NULL, '1999-03-29 0:00:00', '1999-03-29 8:00:00', NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, '6000300?', 0, 0, NULL, 0,
0, 0, 0, 0, 2, 'N', NULL, NULL, NULL,
NULL, NULL, 0, 'N', NULL, NULL, NULL, NULL, NULL,
NULL, 'N', 'PM', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL)
go

create table pm (
pmnum varchar (8) not null ,
description varchar (50) null ,
eqnum varchar (8) null ,
firstdate datetime null ,
lastcompdate datetime null ,
laststartdate datetime null ,
usetargetdate varchar (1) not null ,
lastmeterreading decimal(15,2) not null ,
lastmeterdate datetime null ,
frequency integer not null ,
meterfrequency decimal(15,2) not null ,
pmcounter integer not null ,
priority integer not null ,
worktype varchar (5) null ,
jpnum varchar (10) null ,
jpseqinuse varchar (1) not null ,
nextdate datetime null ,
pm17 varchar (10) null ,
pm18 decimal(15,2) null ,
changedate datetime not null ,
changeby varchar (18) not null ,
pmeq1 varchar (10) null ,
pm1 varchar (8) not null ,
pm2 varchar (8) not null ,
pm3 varchar (10) null ,
pm4 datetime null ,
pm5 decimal(15,2) null ,
ldkey integer null ,
supervisor varchar (8) null ,
calendar varchar (8) null ,
crewid varchar (8) null ,
interruptable varchar (1) null ,
downtime varchar (1) null ,
pm6 varchar (10) null ,
pm7 varchar (10) null ,
pm8 varchar (10) null ,
pm9 decimal(10,2) null ,
pm10 varchar (10) null ,
pmeq2 datetime null ,
pmeq3 decimal(15,2) null ,
pmjp1 varchar (10) null ,
pmjp2 varchar (10) null ,
pmjp3 varchar (10) null ,
pmjp4 decimal(10,2) null ,
pmjp5 datetime null ,
glaccount varchar (20) null ,
location varchar (8) null ,
storeloc varchar (8) null ,
parent varchar (8) null ,
haschildren varchar (1) not null ,
wosequence integer null ,
usefrequency varchar (1) not null ,
route varchar (8) null ,
frequnit varchar (8) not null ,
meterfrequency2 decimal(15,2) not null ,
lastmeterreading2 decimal(15,2) not null ,
lastmeterdate2 datetime null ,
leadtime integer null ,
extdate datetime null ,
adjnextdue varchar (1) null ,
pm11 varchar (10) null ,
pm12 varchar (10) null ,
pm13 varchar (10) null ,
pm14 decimal(10,2) null ,
pm15 integer null ,
pm16 varchar (1) null ,
masterpm varchar (8) null ,
overridemasterupd varchar (1) not null ,
ismasterpm varchar (1) not null ,
masterpmitemnum varchar (30) null ,
applymasterpmtoeq varchar (1) not null ,
applymasterpmtoloc varchar (1) not null ,
updtimebasedfreq varchar (1) not null ,
updstartdate varchar (1) not null ,
updmeter1 varchar (1) not null ,
updmeter2 varchar (1) not null ,
updjpsequence varchar (1) not null ,
updextdate varchar (1) not null ,
updseasonaldates varchar (1) not null ,
wostatus varchar (8) not null ,
seasonstartday smallint null ,
seasonstartmonth varchar (16) null ,
seasonendday smallint null ,
seasonendmonth varchar (16) null ,
pmjp6 varchar (10) null ,
pmjp7 varchar (10) null ,
pmjp8 varchar (10) null ,
pmjp9 decimal(10,2) null ,
pmjp10 datetime null ,
rowstamp timestamp
)
go

insert into pm
( pmnum, description, eqnum, firstdate, lastcompdate, laststartdate,
usetargetdate, lastmeterreading, lastmeterdate,
frequency, meterfrequency, pmcounter, priority, worktype, jpnum,
jpseqinuse, nextdate, pm17,
pm18, changedate, changeby, pmeq1, pm1, pm2, pm3, pm4, pm5,
ldkey, supervisor, calendar, crewid, interruptable, downtime, pm6,
pm7, pm8,
pm9, pm10, pmeq2, pmeq3, pmjp1, pmjp2, pmjp3, pmjp4, pmjp5,
glaccount, location, storeloc, parent, haschildren, wosequence,
usefrequency, route, frequnit,
meterfrequency2, lastmeterreading2, lastmeterdate2, leadtime, extdate,
adjnextdue, pm11, pm12, pm13,
pm14, pm15, pm16, masterpm, overridemasterupd, ismasterpm,
masterpmitemnum, applymasterpmtoeq, applymasterpmtoloc,
updtimebasedfreq, updstartdate, updmeter1, updmeter2, updjpsequence,
updextdate, updseasonaldates, wostatus, seasonstartday,
seasonstartmonth, seasonendday, seasonendmonth, pmjp6, pmjp7, pmjp8,
pmjp9, pmjp10)
values
( 'PM-CONV2', 'Conveyor Overhaul- Conveyor #2', '12700', '1999-03-03
00:00:00', '1996-11-13 00:00:00', '1999-03-30 00:00:00', 'Y', 0, NULL,
90, 0, 1, 8, 'PM', 'JP1314A', 'Y', '1999-06-28 00:00:00', '332',
NULL, '1999-03-30 18:40:00', 'MAXIMO', NULL, 'MAINT', 'PM', NULL,
NULL, NULL,
NULL, NULL, NULL, NULL, 'N', 'Y', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, 'CENTRAL', NULL, 'N', NULL, 'N', NULL, 'DAYS',
0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, 'N', 'N', NULL, 'Y', 'Y',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'WSCH', NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
go

Hugo Kornelis wrote:
> On 20 Jan 2005 09:07:57 -0800, Martin wrote:
> >Hello Guys,
> >Thank you for all your feedback!.
> >Below is the trigger that I'm using and Hugo is right there are many
> >PMs on WOs so it updates all the WO that matches the PMNUM..
> (sniup trigger code)
> Hi Martin,
> The code you posted is even worse: it will update ALL rows currently
in
> the workorder table. All these rows will have their
woassignmntqueueid and
> their problemcode set to PM2 and PM1 from a pm row that matches one
of the
> inserted rows - and if multiple rows are inserted, the trigger will
just
> choose one, semi-randomly.
> I'm quite sure that this is not what you want - but I have no idea
what
> you do want.
>
> >Any way, here is now the situation, these fields, woassignmntqueueid
> >and problemcode are required (NOT NULL ALLOWED) on the workorder
table;
> >so, this trigger never executes.
> This conclusion is wrong. Whether these rows allow NULLS or not has
> nothing to do with the firing of this trigger. As soon as an INSERT
> statement is run against the workorder table, this trigger *WILL*
run, and
> it *WILL* attempt to update *all* rows in workorder.
> Of course, if the chosen value for either woassignmntqueueid or
> problemcode happens to be NULL, the update will fail, causing an
error in
> the trigger and a rollback of the entire transaction (including the
insert
> statement that caused the trigger to fire). But the trigger DOES
execute!
>
> > What do I need to do, to update only
> >the current workorder passing pm.pm2 and pm.pm1 to
woassignmntqueueid
> >and problemcode.
> I'mm sorry, but your narrative is not sufficient to explain your
exact
> requirements. I suggest you post
> * The structure of all related tables (as CREATE TABLE statements,
> including datatypes, constraints and properties; irrelevant columns
may be
> omitted, especially if there are lots of them),
> * Some illustrative sample data (as INSERT statements, so that I can
use
> cut and paste to run the code in Query Analyzer and recreate your
sample
> data on my test database),
> * The required output, and
> * A concise description of the business problem you're trying to
solve.
> Check out this site as well: http://www.aspfaq.com/5006.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 24 Jan 2005 05:31:04 -0800, Martin wrote:

(snip)
>When I generate the PM for the site I have to do it after hours, I
>configure the database and make those two fields to allow null values
>then I run update queries to populate them, and then I reconfigure the
>DB so these two fields are required again. The other problem is that if
>we add or replace a piece of equipment and we need a PM work order
>immediately, I can't happen.
>That is the main reason I need to create this trigger.
>My intention is when I'm on the PM screen and run the automate
>routine to Generate or Create PM work order, this trigger will pass the
>data from the PM table to the workorder table

Hi Martin,

Based on what I read, it appears that you're fighting the symptoms instead
of addressing the cause. It seems to me that the problem is that the code
that generates work orders from PM entries fails to provide the
problemcode and assignmentqueue, even though they ARE available in the PM
table. Could you post the code that generates new work orders from the
rows in the PM table? I guess that THAT is where the real key to solving
your problem lies.

>Workorder.problemcode = pm.pm1
>Workorder.woassignmntqueueid = pm.pm2
>PM Table
>Pmnun Description Pm1 Pm2
>Hvac001 Monthly A/C Unit PM HVAC MAINT
>Workorder Table
>Wonum Description Pmnum problemcode woassignmntqueueid
>1234567 Monthly A/C Unit PM Hvac001 HVAC MAINT
>I have about 500 PM records and each can have many records on the
>workorder table. The PMNUM field is the key field on the PM table and
>a foreign key on the workorder table.

In the mean time, the above contains the info I need to help you with the
trigger. Since there is a foreign key from Workorder to PM, it's possible
to find the one and only PM row that a workorder should be coupled to and
take PM1 and PM2 from that row.

The code below won't update the assignment queue or problemcode values if
no Pmnum is specified in the new row. I assume that Omnum is only present
if a workorder is generated for preventive maintenance and that assignment
queue and workorder should not be changed for other work orders.

If you need the ability to override the problemcode and assignmentqueue
from the PM tables, you need to make two changes:
* change your frontend code so that overriding values for problemcode and
assignment queue can be included in the INSERT statement
* change the SET clauses to (using problemcode as an example)
SET problemcode = COALESCE (w.problemcode, P.PM1)
this will ensure that the value entered is retained, bot if no value is
entered (the value is NULL), it will be replaced by the PM1 value.

CREATE TRIGGER GENERATE_PM_WO ON [workorder]
FOR INSERT
AS
BEGIN
IF @.@.rowcount = 0
RETURN
UPDATE w
SET woassignmntqueueid = P.PM2,
problemcode = P.PM1
FROM workorder AS w
INNER JOIN pm AS p
ON p.Pmnum = w.Pmnum
INNER JOIN inserted AS i
ON i.Wonum = w.Wonum
-- Note - the inner join to inserted ensures only new rows are affected.
-- This could just as well have been written as an EXISTS or IN subquery.
END

(untested)

Best, Hugo
--

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

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up
4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank you
On Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:
>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

insert trigger and if update(x)

I've not done a lot of triggers, so perhaps this is a dumb question,
but here goes.
BOL says "if update(x)" is for both insert and update triggers, but I
don't understand what role it plays in insert triggers. My little
experiments suggest that in an insert trigger, "if update(x)" fires if
x has a value, if x is null, or if x is omitted from the insert list.
So, is that right, will an "if update(x)" ALWAYS fire inside of an
insert trigger?
(I ask because I'm looking at some legacy code that does just that)
Thanks!
JoshIf an insert only inserts values into some of the tables columns, (Say the
missing ones are nullable) then the if update(x)"on one of the nulleable
columns that was not affected will be false... But I'm wondering what will
happen if the missing column is NOT nulleable and has a default value... I'l
l
run a test...
"JRStern" wrote:

> I've not done a lot of triggers, so perhaps this is a dumb question,
> but here goes.
> BOL says "if update(x)" is for both insert and update triggers, but I
> don't understand what role it plays in insert triggers. My little
> experiments suggest that in an insert trigger, "if update(x)" fires if
> x has a value, if x is null, or if x is omitted from the insert list.
> So, is that right, will an "if update(x)" ALWAYS fire inside of an
> insert trigger?
> (I ask because I'm looking at some legacy code that does just that)
> Thanks!
>
> Josh
>|||See "CREATE TRIGGER" in BOL, it is explained there.
AMB
"JRStern" wrote:

> I've not done a lot of triggers, so perhaps this is a dumb question,
> but here goes.
> BOL says "if update(x)" is for both insert and update triggers, but I
> don't understand what role it plays in insert triggers. My little
> experiments suggest that in an insert trigger, "if update(x)" fires if
> x has a value, if x is null, or if x is omitted from the insert list.
> So, is that right, will an "if update(x)" ALWAYS fire inside of an
> insert trigger?
> (I ask because I'm looking at some legacy code that does just that)
> Thanks!
>
> Josh
>|||Did a test, I was wrong, all columns show as updated in an insert, even
nulleable columns without default values that are not mentioned in the
insert.
"CBretana" wrote:
> If an insert only inserts values into some of the tables columns, (Say the
> missing ones are nullable) then the if update(x)"on one of the nulleable
> columns that was not affected will be false... But I'm wondering what will
> happen if the missing column is NOT nulleable and has a default value... I
'll
> run a test...
> "JRStern" wrote:
>|||The BOL specifically says:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
The IF UPDATE (column_name) clause in the definition of a trigger can be
used to determine if an INSERT or UPDATE statement affected a specific colum
n
in the table. The clause evaluates to TRUE whenever the column is assigned a
value.
which pretty strongly implies that only columns affected by the insert will
trigger the If Update() test, but the test I just ran
Create Table Test
(A Int Not Null,
B Int Null,
C Int Null Default 0)
-- --
CREATE TRIGGER Test_Trigger1
ON dbo.Test
FOR INSERT, UPDATE
AS
If UPDATE (A) Print 'A was updated'
If UPDATE (B) Print ' B was updated'
If UPDATE (C) Print' C was updated'
GO
-- ---
Insert Test(a) Values(23)
Pretty much shows that not the case...
"CBretana" wrote:
> If an insert only inserts values into some of the tables columns, (Say the
> missing ones are nullable) then the if update(x)"on one of the nulleable
> columns that was not affected will be false... But I'm wondering what will
> happen if the missing column is NOT nulleable and has a default value... I
'll
> run a test...
> "JRStern" wrote:
>|||On Wed, 9 Mar 2005 16:25:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>See "CREATE TRIGGER" in BOL, it is explained there.
I *guess* that says it always fires in an insert trigger, which is
consistent with what I and CBretana are seeing.
Various "why ..." questions come to mind ...
Thanks for the replies.
J.
>AMB
>"JRStern" wrote:
>|||On Wed, 9 Mar 2005 16:41:02 -0800, "CBretana"
<cbretana@.areteIndNOSPAM.com> wrote:
>The BOL specifically says:
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>The IF UPDATE (column_name) clause in the definition of a trigger can be
>used to determine if an INSERT or UPDATE statement affected a specific colu
mn
>in the table. The clause evaluates to TRUE whenever the column is assigned
a
>value.
Read on:
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
IF UPDATE will return the TRUE value in INSERT actions because the
columns have either explicit values or implicit (NULL) values
inserted.
Also includes explicit NULL values, it seems.
I was going to ask if this is ANSI or something, but I don't think
there is any ANSI standard at all on triggers.
Anyone know what Oracle does?
Just asking crazy questions at this point, it seems clear enough what
SQLServer is doing, though the "why" is still vague.
J.|||JR, I found the reference That Alex mentioned, and it is clear, and says
exactly what we experienced, but other references imply what we <wrongly>
thought would happen...
"JRStern" wrote:

> On Wed, 9 Mar 2005 16:25:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
> I *guess* that says it always fires in an insert trigger, which is
> consistent with what I and CBretana are seeing.
> Various "why ..." questions come to mind ...
> Thanks for the replies.
> J.
>
>|||JRStern wrote:
> On Wed, 9 Mar 2005 16:25:04 -0800, "Alejandro Mesa"
> <AlejandroMesa@.discussions.microsoft.com> wrote:
> I *guess* that says it always fires in an insert trigger, which is
> consistent with what I and CBretana are seeing.
> Various "why ..." questions come to mind ...
> Thanks for the replies.
>
Not sure myself. Tried even with an AFTER TRIGGER, but had the same
results. All I can think of is that it covers the case where a user
wants an insert/update trigger and needs to use the function (or
COLUMNS_UPDATED() which has the same behavior).
I agree with the intended behavior. All columns are affected on an
insert operation. The BOL examples should probably use the function on
an update trigger for clarity.
David Gugick
Imceda Software
www.imceda.com

Insert Trigger

Hi,
I am new to triggers and would like your help
I am trying to write an insert trigger on a table, so that when a record is
inserted in table1 a dummy record is inserted in table2
eg: table "master" has a record with fields "1', "Honda", "1998"
When this record is inserted into the "master" table, I need it to insert
another record in the "userlog" table, with the following fields "1",
"Honda", "datatimestamp". The trigger inserts the record when I use
hardcoded values. However, I do not know how to reference the values that
were inserted into the "master" table and then insert those values into the
"userlog" table.
Please help
Thanks
- RichIn triggers, there are 2 "special" tables in memory, that
exist for use only inside triggerville, called INSERTED or
DELETED. These tables are maintained for you by SQL
Server, and the layout of columns, datatypes matches back
exactly to the "master" table. Refer to these like other
tables (e.g. select * from INSERTED) INSIDE the trigger of
the table being modified...
On an INSERT, the new values inserted are stored in
INSERTED only.
On an UPDATE, the old values are stored in DELETED, and
new values are stored in INSERTED.
On a DELETE, the old values are stored in DELETED only.
Remember that a trigger is executed once per SQL action
against that table, so if your statement inserts 100 rows
to table_X, the INSERT trigger for table_X is fired ONCE,
not 100 times...
Bruce
>--Original Message--
>Hi,
>I am new to triggers and would like your help
>I am trying to write an insert trigger on a table, so
that when a record is
>inserted in table1 a dummy record is inserted in table2
>eg: table "master" has a record with
fields "1', "Honda", "1998"
> When this record is inserted into the "master" table, I
need it to insert
>another record in the "userlog" table, with the following
fields "1",
>"Honda", "datatimestamp". The trigger inserts the record
when I use
>hardcoded values. However, I do not know how to
reference the values that
>were inserted into the "master" table and then insert
those values into the
>"userlog" table.
>Please help
>Thanks
>- Rich
>
>.
>|||Bruce
Thanks you very much. This helped
- Rich
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:097b01c3adfb$5205bc50$a601280a@.phx.gbl...
> In triggers, there are 2 "special" tables in memory, that
> exist for use only inside triggerville, called INSERTED or
> DELETED. These tables are maintained for you by SQL
> Server, and the layout of columns, datatypes matches back
> exactly to the "master" table. Refer to these like other
> tables (e.g. select * from INSERTED) INSIDE the trigger of
> the table being modified...
> On an INSERT, the new values inserted are stored in
> INSERTED only.
> On an UPDATE, the old values are stored in DELETED, and
> new values are stored in INSERTED.
> On a DELETE, the old values are stored in DELETED only.
> Remember that a trigger is executed once per SQL action
> against that table, so if your statement inserts 100 rows
> to table_X, the INSERT trigger for table_X is fired ONCE,
> not 100 times...
> Bruce
> >--Original Message--
> >Hi,
> >I am new to triggers and would like your help
> >
> >I am trying to write an insert trigger on a table, so
> that when a record is
> >inserted in table1 a dummy record is inserted in table2
> >
> >eg: table "master" has a record with
> fields "1', "Honda", "1998"
> > When this record is inserted into the "master" table, I
> need it to insert
> >another record in the "userlog" table, with the following
> fields "1",
> >"Honda", "datatimestamp". The trigger inserts the record
> when I use
> >hardcoded values. However, I do not know how to
> reference the values that
> >were inserted into the "master" table and then insert
> those values into the
> >"userlog" table.
> >Please help
> >Thanks
> >- Rich
> >
> >
> >.
> >sql

Insert query firing Insert & Update trigger at the same time.

Hello All,
I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp.

Insert and Update trigger work fine when i have only one of them defined.

However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and has the same timestamp in the audit table.

Insert trigger goes as
CREATE TRIGGER InsRecord ON [dbo].[tableA]
AFTER INSERT
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Insert',GetDate() from inserted

Update trigger goes as
CREATE TRIGGER UpdRecord ON [dbo].[tableA]
FOR UPDATE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Update',GetDate() from inserted

Delete Trigger goes as
CREATE TRIGGER delRecord ON [dbo].[tableA]
FOR DELETE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Delete',GetDate() from deleted

Note:This tableA has relations with 2 other tables on 1 field each from each table but i don't think it should matter.

Please advise how to prevent it.CREATE TRIGGER alteredRecord ON [dbo].[tableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN

...declare lngIns & lngDel

SELECT lngIns=count(col1)
from inserted

select lngDel=count(col1)
from deleted

IF lngIns>0 and lngDel=0
...inserted
else if lngIns>0 and lngDel>0
...updated
else if lngIns=0 and lngDel>0
...deleted
end

END