Conditional Trigger?

Still new to programming in SQL but need to do the following:

On a table with Provider information need to set a trigger? that will prevent anyone from using our own taxID number. The problem is that six Providers belong to us and for them that taxID is acceptable.

My question is can a constraint be set that applies to every row in the table except my list of six, if not how would I set up a trigger that will fire for INSERT and UPDATE that will exclude the six providerID's?

tblProvider is table
ProviderID is Unique Key
TaxID is field name for the tax id
Application only updates or inserts one row at a time

Thanks,

BrentIf I understand you right, try this... I assumed some information for this example... this constraint will let the TaxID be anything for ProviderIDs between 1 and 6 and restrict it from using TaxID for any other ProviderID

ALTER TABLE tblProvider
ADD CONSTRAINT ck_Provider_TaxID
CHECK (TaxID <> 100 or ProviderID in (1,2,3,4,5,6))

Originally posted by baolive
Still new to programming in SQL but need to do the following:

On a table with Provider information need to set a trigger? that will prevent anyone from using our own taxID number. The problem is that six Providers belong to us and for them that taxID is acceptable.

My question is can a constraint be set that applies to every row in the table except my list of six, if not how would I set up a trigger that will fire for INSERT and UPDATE that will exclude the six providerID's?

tblProvider is table
ProviderID is Unique Key
TaxID is field name for the tax id
Application only updates or inserts one row at a time

Thanks,

Brent|||Thanks for the reponse HueyStLoui.

Tried the code and it seems to be having a problem with the TaxID column. As it verifies against the first entry it gives the following error:

Syntax error converting the varchar value '111-11-1111' to a column of data type int.

(NOTE: the number has been changed as I don't want to post someones SSN).

The TaxID column is a varchar15. Data contained is either in SSN format (NNN_NN_NNNN) or Federal Tax ID (NN-NNNNNNN).

Brent|||Brent,

You will need to change the constraint I posted slightly to match the datatypes of your columns (I just assumed both where integers). If TaxID is a varchar, just put single quotes around the value... (same goes for ProviderID if it is a varchar as well). Note.. you will need to change the ProviderID's listed to be the 6 you mentioned in your original post.

ALTER TABLE tblProvider
ADD CONSTRAINT ck_Provider_TaxID
CHECK (TaxID <> '111-11-1111' or ProviderID in (1,2,3,4,5,6))

Originally posted by baolive
Thanks for the reponse HueyStLoui.

Tried the code and it seems to be having a problem with the TaxID column. As it verifies against the first entry it gives the following error:

Syntax error converting the varchar value '111-11-1111' to a column of data type int.

(NOTE: the number has been changed as I don't want to post someones SSN).

The TaxID column is a varchar15. Data contained is either in SSN format (NNN_NN_NNNN) or Federal Tax ID (NN-NNNNNNN).

Brent|||Thanks,
Looks like that will work for us.

Conditional Trigger

I am trying to write a trigger that will only fire when certain fields are updated. I can't seem to find any threads that relate to this issue. Below is the trigger syntax. Any info would be great. Thx

CREATE TRIGGER tr_feedback_date ON Calls
FOR UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if update (feedback) or update (analyst) or update (coordinator) or update (technical) or update (fixtime)
or update (phonewait) or update (resp)
UPDATE hold_complete
set hold_complete.feedbackdt = getdate()
from inserted with (nolock)
where hold_complete.fkey = inserted.callidThe create trigger statement can take an IF UPDATE ( column ) clause. You can read about it in Books Online.

Another option it to compare the values in the INSERTED table to the values in the DELETED table. Since updates involve both insertions and deletions, your affected record will exist in both and you can compare the differences based on the primary key.

blindman|||Also, there is very useful function COLUMNS_UPDATED() which you can use in trigger for checking what fields were updated.

Conditional Trigger

I'm hoping someone here can help me with this situation...I've looked everywhere and cannot find a solution.

I have two separate databases. Table structures are different. A record is inserted into Table 1 of Database A. Then, when Field 1 in Table 1 is updated, I want my trigger to fire and insert certain values (selected into "inserted") to be inserted into Database B, Table 1.

I have created a trigger that will do just that. Here's my problem:

I need to ensure the trigger only fires if two conditions are met. If the value in Field 1 is updated to one specific value, and if the type of transaction (housed in a separate field) is one of several specific types (linked to the query from a separate table).

Otherwise, I want the trigger to be ignored completely.

Below is my current code. Can anyone here assist?
Thanks in advance for any help,
Dirk

CREATE TRIGGER [ELOGXWALK] ON [dbo].[Appointments]
AFTER UPDATE
AS

SET XACT_ABORT ON

IF UPDATE(Status)
BEGIN

declare @.recnum int
declare @.dateofenc datetime
declare @.timein varchar (5)
declare @.Code varchar (6)
declare @.Descr varchar (25)
declare @.clinic varchar (6)
declare @.status varchar (5)
declare @.Appointment_ID int
declare @.Encounter_Number int
declare @.nextnum int

select @.recnum = fldrec_num, @.dateofEnc = left(appointment_datetime, 11), @.timein=right('0'+convert(varchar,time_in), 4),
@.Code = Code, @.Descr = Descr, @.clinic = 'ONSITE', @.Status = status, @.Appointment_ID = Appointment_ID,
@.Encounter_Number = Encounter_Number from inserted
left join Crosswalk.dbo.EncounterLink on TypeID = Appointment_Type_ID
left join patients on patients.patient_id = inserted.patient_id
left join contacts on contacts.contact_id = patients.contact_id
left join DBOCC.MCH_OHM.XWALK.EMPLOYEE on FLDSSN = contacts.ssn
where Appointment_Type_ID in (select TypeID from Crosswalk.dbo.EncounterLink)
and status = 'A'

set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq
set fldnextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG') + 1
where fldtablename = 'ELOG'

insert into DBOCC.mch_ohm.XWALK.ELOG (FLDREC_NUM, FLDEMPLOYEE, FLDDATE, FLDTIME, FLDTYPE, FLDDESCR, FLDCLINIC, FLDRECNUM, FLDENCNUMBR)
values (@.nextnum, @.recnum, @.dateofenc, @.timein, @.code, @.Descr, @.clinic, @.appointment_id, @.Encounter_Number)

END

IF UPDATE (Encounter_Number)
BEGIN

select @.Appointment_ID = Appointment_ID, @.Encounter_Number = Encounter_Number from inserted
left join DBOCC.MCH_OHM.XWALK.ELOG on FLDRECNUM = inserted.Appointment_ID

update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = @.Encounter_Number
where FLDRECNUM = @.Appointment_ID

ENDcan you provide more specifics on the two conditions?|||First condition. The TypeID referenced in the script must be any one of about 10 different values. These are stored in a separate table (EncounterLink) along with the corresponding value I want to load into Database B. If the TypeID does not equal one of the values in the EncounterLink table, I want the trigger to disregard everything.

Second condition. The Status field should only fire the trigger if a certain value is entered. In this case, only the value "A" should fire the trigger. In the future I will probably want one or two different trigger functions based on different values.

Dirk|||great! your current design will work if one row is updated but will fail (in some fashion) if 2+ rows are updated. Do you want to handle multiple row updates? also, do you want the Encounter_number updated only if the TypeID and Status are okay or are these seperate events?|||I should not have to deal with multiple row updates, because the front end application will only do a single row at a time anyway (clicking a button in the app triggers the change).

I've actually made progress by specifically referencing "inserted" in the where statement of my select and adding an "If @.@.rowcount > 0" and "Begin" command just prior to the set statement. It seems to be working properly all around, but any streamlining suggestions will be more than welcome, as I am not really a DBA - my company justs asks that I play one during working hours!

Dirk|||it sounds like you are on your way...

I would have taken a diffrent approach so the trigger could handle multiple row updates but the bottom line is that you understand the trigger and it works. I would suggest two things though,

First
----------------------------
set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = @.nextnum + 1 where fldtablename = 'ELOG'

or even

begin transaction
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = fldnextnum + 1 where fldtablename = 'ELOG'
select @.nextnum = (fldnextnum - 1) from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG'
commit transaction

And
----------------------------
IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join DBOCC.MCH_OHM.XWALK.ELOG on inserted.Appointment_ID = DBOCC.MCH_OHM.XWALK.ELOG.FLDRECNUM
end
----------------------------

these are minor things but (IMHO) you should save every step you can when writting triggers.|||Thanks for the tips! Both make sense to me, but the second one does not work...

Server: Msg 117, Level 15, State 2, Procedure ELOGXWALK, Line 49
The number name 'DBOCC.MCH_OHM.XWALK.ELOG' contains more than the maximum number of prefixes. The maximum is 3.

Dirk|||ARG!!! I do that sometimes.
I don't use liked servers that much but I think is should be something like

IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join OPENQUERY(DBOCC, select FLDRECNUM from MCH_OHM.XWALK.ELOG) elog on inserted.Appointment_ID = elog.FLDRECNUM
end

What you have is just fine though.

Conditional SQL Triggers

Hi, I've been handed a task at work where I need to use SQL triggers to solve the problem.

I need to be able to run a Trigger when, and only when, a cell in a specific column in a row changes to a specific value.

For instance, say I have the following table:
CREATE TABLE source
(
ID tinyint NOT NULL,
contacttype tinyint NOT NULL,
)

If one of the rows is UPDATE'd to contacttype = 2, I want to fire a trigger, but not if it changes to anything else.

How can this be performed?You can attach a trigger to a table to respond to inserts, deletes or updates. I think you can narrow it down to a specific column (if columns_updated). Anything else goes in the trigger itself. See BOL for TRIGGER.|||create trigger blah on update
as
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype FROM inserted = 2
BEGIN
...insert code here...
END
END|||Understand, the TRIGGER will always fire. As shown, you want to control the logic inside the trigger.

What action do you need to take?

Just make sure the affected rows use the id of that row as the reason to modify that data|||Thanks for all the replies!

I tried mitchell007's code, and it helped a lot. I am now able to run SQL statements if a certain column is updated.
But I had a problem with the line: IF SELECT contacttype FROM inserted = 2
This results in a parse error. "Incorrect syntax near the keyword SELECT" and "Incorrect syntax near '='."

Here is my trigger code:
CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype from inserted = 2
BEGIN
print 'contacttype modified!'
END
END

Brett, my ultimate goal is to detect when a row in a contact table changes the value of the 'contacttype' column. If a row is created with contacttype = 2, or if an existing row is updated to that value, I want to create a new row in a different database.

Also, how can I know which row was altered? When all the trigger filters pass, I want to extract the updated row, and insert most of it's data into a different database.|||Okay, I've been working with this for some hours now, and have come a little further, but still have some obstacles to climb over.

First, when I try to use inserted.contacttype to get the value from the updated table, and into my new table, I get a "Error 128: The name 'contacttype' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

This works:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (1,2,3,4)
END

But this doesn't:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (inserted.contacttype,2,3,4)
END

Second, I am still struggeling with getting the trigger to run the INSERT statement only when contacttype changes to a specific number. Right now the INSERT fires when the contacttype field is updated to any value.|||The select statement after the update is not necessary. Try this:


CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF inserted.contacttype = 2
BEGIN
<perform needed operations here>
END
END|||Here is the complete trigger I've written so far, with tomh53's suggestion. With the "IF inserted.category_idx = 2" line I get a "Error 107: The column prefix 'inserted' does not match with a table name or alias name used in the query."

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
IF inserted.category_idx = 2
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma SELECT department, contact_id, name, number1, number2, business_idx, orgNr from inserted
END
END|||Scalpel ... my apologies for ** bad ** code. Try this:

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma
SELECT department, contact_id, name, number1, number2, business_idx, orgNr
FROM inserted
WHERE inserted.category_idx = 2
END

Conditional inserts in trigger

Can I have a trigger that inserts into 1 of 3 different tables based on a
column being inserted on the driving table?
For example, I issue
INSERT INTO dbo.People (SSN, CategoryCode)
VALUES (123456789, 3)
If CategoryCode inserted is 3, 10 or 11 then I need to
INSERT INTO dbo.ClientInfo (PeopleID)
VALUES (inserted.PeopleID)
If CategoryCode inserted is 1 then I need to
INSERT INTO dbo.ApplicantInfo (PeopleID)
VALUES (inserted.PeopleID)
etc.
One point that may or may not be important is that the original PeopleID is
assigned in an existing insert trigger and is a random number.
Thanks.
Davidyes - see BOL for more on CREATE TRIGGER, but e.g.
create trigger yourtrigger on People for insert
as
begin
insert into dbo.ApplicantInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode=1
insert into dob.ClientInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode in (3, 10, 11)
end
David Chase wrote:
> Can I have a trigger that inserts into 1 of 3 different tables based on a
> column being inserted on the driving table?
> For example, I issue
> INSERT INTO dbo.People (SSN, CategoryCode)
> VALUES (123456789, 3)
> If CategoryCode inserted is 3, 10 or 11 then I need to
> INSERT INTO dbo.ClientInfo (PeopleID)
> VALUES (inserted.PeopleID)
> If CategoryCode inserted is 1 then I need to
> INSERT INTO dbo.ApplicantInfo (PeopleID)
> VALUES (inserted.PeopleID)
> etc.
> One point that may or may not be important is that the original PeopleID i
s
> assigned in an existing insert trigger and is a random number.
> Thanks.
> David
>|||That doesn't work. I get an error when it tries to create ClientInfo
because ClientInfo table has referrential integrity rule that requires
matching record in People table. Evidently, ref. integrity check does not
know that People table record exists yet. Below is my trigger code, if that
helps.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uHtw5DHHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> yes - see BOL for more on CREATE TRIGGER, but e.g.
> create trigger yourtrigger on People for insert
> as
> begin
> insert into dbo.ApplicantInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode=1
> insert into dob.ClientInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode in (3, 10, 11)
> end
> David Chase wrote:|||David Chase (dlchase@.lifetimeinc.com) writes:
> That doesn't work. I get an error when it tries to create ClientInfo
> because ClientInfo table has referrential integrity rule that requires
> matching record in People table. Evidently, ref. integrity check does
> not know that People table record exists yet. Below is my trigger code,
> if that helps.
Set up the FK to have UPDATE ON CASCADE.
Or instead of an UPDATE, perform first an INSERT, update the childre,
and then delete the original.

> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Keep in mind that a trigger fires once per statement, and thus inserted
can hold many rows.
A better bet for a random number is probably checksum(newid()).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Conditional For Insert Trigger

Sort of new to this, hoping for some help here.
I've got a table which you insert Scores and comments for Different
Issues. Each User may only input ONE score, but many comments per
issue.
I'm attempting to run a trigger which looks at the distinct users (in
the table) and then will either allow the comment AND score to be
Inserted if the User hasn't put in a score for that issue yet. OR if
the User already HAS input a Score, set the Score to NULL and allow the
rest of the record to be inserted.
My trigger works, but instead of letting the *New* User to put in the
score, it just nulls it.
Sorry if this is kind of long, but I hope I've explained it
sufficiently. I hope someone can help me out. Thanks in advance
This is my trigger :
CREATE TRIGGER trCheckUserResponses
ON dbo.tblIssues_Responses
FOR INSERT
AS
IF (SELECT UserID from Inserted) not in (select distinct UserID from
tblIssues_responses)
BEGIN
Update tblIssues_Responses
Set tblIssues_Responses.Score= Inserted.Score
From tblIssues_Responses AS A, Inserted AS B
Where A.Response_ID = B.Response_ID
END
ELSE
BEGIN
Update tblIssues_Responses
Set tblIssues_Responses.Score = NULL
From tblIssues_Responses AS A, Inserted AS B
Where A.Response_ID = B.Response_ID
ENDPlease post DDL and sample data.
ML
http://milambda.blogspot.com/

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
Todd
Can you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd
|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of th
e
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted r
ow
> to an audit table. .Net is calling the same stored procedure to update th
e
> same row in the base table 4 times. The stored procedure subtracts a pass
ed
> in value from a column in the base table. After the code runs, the value
in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of
the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger t
o
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],
[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[Tota
lAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
>

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:
> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
> > I have a table that I am auditing by having a trigger insert the Deleted row
> > to an audit table. .Net is calling the same stored procedure to update the
> > same row in the base table 4 times. The stored procedure subtracts a passed
> > in value from a column in the base table. After the code runs, the value in
> > the base table is correct, but the audit rows appear to show the the first
> > update subtracted first two amounts. The other weird thing is the time
> > stamp, which is generated from a getdate() is exactly the same for two of the
> > rows.
> >
> > How tightly is the trigger code tied to the code that causes the trigger to
> > fire? We have tried playing with isolation levels on the .Net transaction
> > and this hasn't helped. Have a tripped on a bug, or am I doing something
> > wrong. Thanks for the help.
> >
> > Todd