472,325 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,325 software developers and data experts.

Trigger on Insert made a strage information,

I have a problem while I create a trigger to my table. My objective is getting any change made to my table [tblCR] and record it in to another table [tblLogWeb]. My [tblCR] have thousands records before I add new trigger to this table, everything work fine means, when I manual insert data into table the last row of inserting is what I am doing but after I add trigger on inserting to this table and I do next manual insert data into the last row in Enterprise Manager, I got a strange info, it is shown the row somewhere of my table but not the current value which I just added. If I run SQL to show the data from table again, I saw the last value which that I just added at the bottom.

The following script is the schema of my table and also its triggers

------------------------ table schema -------------------------

CREATE TABLE [dbo].[tblCR] (
[CRID] [int] IDENTITY (1, 1) NOT NULL ,
[FlightID] [smallint] NULL ,
[CRDate] [smalldatetime] NULL ,
[CRReceiveID] [smallint] NULL ,
[CRCountingPersonID] [nvarchar] (50) NULL ,
[CRAirlineEmployeeID] [nvarchar] (50) NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblCRD] WITH NOCHECK ADD
CONSTRAINT [PK_tblCRD] PRIMARY KEY CLUSTERED
(
[CRDID]
) ON [PRIMARY]
GO


----------------------- trigger ---------------------------

--Insert
--DROP TRIGGER trg_insert_tblCR
CREATE TRIGGER trg_insert_tblCR ON [dbo].[tblCR]
FOR INSERT
AS
INSERT INTO tblLogWeb(userTransaction)
SELECT 'INSERT|tblCR|'+ RTRIM(LTRIM(STR(CRID)))
FROM inserted
GO


--update
--DROP TRIGGER trg_update_tblCR
CREATE TRIGGER trg_update_tblCR ON [dbo].[tblCR]
FOR UPDATE
AS
INSERT INTO tblLogWeb(userTransaction)
SELECT 'UPDATE|tblCR|'+ RTRIM(LTRIM(STR(CRID)))
FROM inserted
GO


--delete
--DROP TRIGGER trg_delete_tblCR
CREATE TRIGGER trg_delete_tblCR ON [dbo].[tblCR]
FOR DELETE
AS
INSERT INTO tblLogWeb(userTransaction)
SELECT 'DELETE|tblCR|'+ RTRIM(LTRIM(STR(CRID)))
FROM deleted
GO


Could you please tell me what happend? And how could I do to resolve this problem? There have another application in Ms Access which used that value, so if it doesn't show the current value of added, but the some other value or the row, it cause a big trouble.

Best regards,

Veasna
Dec 24 '07 #1
1 2159
I have a problem while I create a trigger to my table. My objective is getting any change made to my table [tblCR] and record it in to another table [tblLogWeb]. My [tblCR] have thousands records before I add new trigger to this table, everything work fine means, when I manual insert data into table the last row of inserting is what I am doing but after I add trigger on inserting to this table and I do next manual insert data into the last row in Enterprise Manager, I got a strange info, it is shown the row somewhere of my table but not the current value which I just added. If I run SQL to show the data from table again, I saw the last value which that I just added at the bottom.

The following script is the schema of my table and also its triggers

------------------------ table schema -------------------------

CREATE TABLE [dbo].[tblCR] (
[CRID] [int] IDENTITY (1, 1) NOT NULL ,
[FlightID] [smallint] NULL ,
[CRDate] [smalldatetime] NULL ,
[CRReceiveID] [smallint] NULL ,
[CRCountingPersonID] [nvarchar] (50) NULL ,
[CRAirlineEmployeeID] [nvarchar] (50) NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblCRD] WITH NOCHECK ADD
CONSTRAINT [PK_tblCRD] PRIMARY KEY CLUSTERED
(
[CRDID]
) ON [PRIMARY]
GO


----------------------- trigger ---------------------------

--Insert
--DROP TRIGGER trg_insert_tblCR
CREATE TRIGGER trg_insert_tblCR ON [dbo].[tblCR]
FOR INSERT
AS
INSERT INTO tblLogWeb(userTransaction)
SELECT 'INSERT|tblCR|'+ RTRIM(LTRIM(STR(CRID)))
FROM inserted
GO


--update
--DROP TRIGGER trg_update_tblCR
CREATE TRIGGER trg_update_tblCR ON [dbo].[tblCR]
FOR UPDATE
AS
INSERT INTO tblLogWeb(userTransaction)
SELECT 'UPDATE|tblCR|'+ RTRIM(LTRIM(STR(CRID)))
FROM inserted
GO


--delete
--DROP TRIGGER trg_delete_tblCR
CREATE TRIGGER trg_delete_tblCR ON [dbo].[tblCR]
FOR DELETE
AS
INSERT INTO tblLogWeb(userTransaction)
SELECT 'DELETE|tblCR|'+ RTRIM(LTRIM(STR(CRID)))
FROM deleted
GO


Could you please tell me what happend? And how could I do to resolve this problem? There have another application in Ms Access which used that value, so if it doesn't show the current value of added, but the some other value or the row, it cause a big trouble.

Best regards,

Veasna


I think the column type is IDENTITY so that its creating problem.

More info is here http://support.microsoft.com/kb/319699
Dec 26 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Tom Urbanowicz | last post by:
I have a trigger. Each time the triggering event fires, I want to insert some information into another 'audit-like' table (not at all related to...
2
by: Trevor Fairchild | last post by:
I am trying to create a very minimal auditing system for a series of databases. I am in the process of writing Update triggers for 5 Tablse. I...
2
by: Elvira Zeinalova | last post by:
Hei, We have 2 MS SQL SERVER 2000 installed on 2 different servers (2 separated machines). I am triing to connect them så that when one row is...
7
by: yoyo | last post by:
Is there anyway to delay a trigger from firing for a few seconds? I have an after insert trigger on table1, which selects information from table2...
6
by: Jchick | last post by:
Im a newbie with a sql table in a MSDE database that contains fields of CustName, BrokerName, Type, Status. I am trying to write a trigger that...
0
by: magnolia | last post by:
i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record...
9
by: Chico Che | last post by:
Have a table that has following fields (pkid, field1, field2, field3, field4). I need to create a trigger that will insert a row into another table...
11
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR...
1
by: JB4375 | last post by:
I've created an ASP gridview and made it editable. It displays the information that was entered into a SQL table plus a time stamp and the...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.