By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,079 Members | 916 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,079 IT Pros & Developers. It's quick & easy.

Trigger on Insert made a strage information,

P: 3
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
Share this Question
Share on Google+
1 Reply


vksingh24
P: 21
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

Post your reply

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