Re: Killed by Triggers

Without seeing your DDL, my guess is that your have user names longer than
your CREATEDBY column was defined to hold.

What do you mean 'detail record was attempted'?

If a trigger fails, it will rollback the operation that caused the trigger
to be filed.

We really need more details here to give you more precise answers.

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


"Will" <Will@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9BEE2EB6-B81B-43F8-9701-A24FAADB604E@xxxxxxxxxxxxxxxx
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: Killed by Triggers
    ... CURRENT_USER is probably too long for my CREATEDBY field. ... MODIFYing and EXECUTing a disabled trigger enables it. ... Kalen Delaney, SQL Server MVP ... to the header table the error message was "String or Binary Data would be ...
    (microsoft.public.sqlserver.security)
  • Re: Killed by Triggers
    ... When I removed the trigger the error disappeared. ... The second situation is when a detail record was attempted, after the header ... If you don't get an error message, ... UPDATE TABLE01 SET CREATEDBY = CURRENT_USER, ...
    (microsoft.public.sqlserver.security)
  • 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: 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: If all tick boxes in an order = yes then me=complete? How do I
    ... to use to trigger the update. ... > implement DSum and DCount - my access skills haven't stretched quite THAT far ... A quick and dirty check would be to DCount number of detail ... >>It may be argued though that it's worth adding a boolean field at header ...
    (microsoft.public.access.formscoding)