Re: Killed by Triggers

Kalen, thanks for the quick reply.

The affected tables are a header/detail relationship. When trying to write
to the header table the error message was "String or Binary Data would be
truncated". When I removed the trigger the error disappeared.

The second situation is when a detail record was attempted, after the header
record. The user gets no error, but the record is never written. Disabling
the triggers also resolved this issue.

Records are written through stored procedures and only 1 record is written
to each table in a transaction.

--
-Will


"Kalen Delaney" wrote:

Hi Will

What do you mean that users could not write to the tables? Do you get an
error? What does it say? If you don't get an error message, what are your
symptoms?

Please note that your trigger seems to assume that there will only be one
row inserted. You'll have problems if multiple rows are inserted in a single
operation.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Will" <Will@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:100B4CDF-AD27-4E0B-AF30-DC40D4E55AB3@xxxxxxxxxxxxxxxx
I have INSERT and UPDATE triggers on my SQL2005 tables which record some
audit trail information. I noticed that they didn't seem to be working so
I
reviewed them. They looked okay. I resaved them and carried on. They used
to
work.

Then I found that users could no longer write to these tables. When I
disabled the triggers, users could again write records. A sample trigger
is:


ALTER TRIGGER [tr_TABLE01_INSERT]
ON [dbo].[TABLE01]
AFTER INSERT
AS
BEGIN
DECLARE @ID AS INTEGER
SET NOCOUNT ON;
SELECT @ID = IDFIELD FROM inserted
IF @ID IS NOT NULL
UPDATE TABLE01 SET CREATEDBY = CURRENT_USER,
CREATEDATE = GETDATE()
WHERE IDFIELD = @ID
END

What could have I done to cause this?


--
-Will



.

Relevant Pages

  • Re: Confusing POST behavior -- doing it twice?
    ... The header you set will just do that: ... > 1 - When I start, there is no error message. ... Some just fetch the HTML from cache and display that. ... I have no idea how the current mostly used browser behave. ...
    (comp.lang.php)
  • Implementation design advice needed
    ... I have a system that stores data in a propietary format that has a number of ... external views and tables in order to transpose the data into client ... When data is updated according to the header table I need ... Change my BUS/AUS trigger logic and use a temp global table to store each ...
    (comp.databases.oracle.server)
  • Re: resuming swsusp twice
    ... it should only be an error message, ... the console was left on the swsusp VT ... [snip severe ext3 damage] ... it would verify that the header and the image matched, ...
    (Linux-Kernel)
  • Re: AfterUpdate?
    ... still buffered at the header level. ... the detail fields afterupdate triggered event's subsequent SELECT, ... Changing a control's Value via VBA code does not trigger any ...
    (microsoft.public.access.formscoding)
  • Re: NEED TO CALL AS400 S.P
    ... That is to use a trigger on a table and perform an INSERT/UPDATE which will execute the trigger. ... What an error message! ... > what your OLE DB provider is feed the AS400 with? ... > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se ...
    (microsoft.public.data.oledb)