create a Insert trigger

I need to create a trigger on tblBag_data to add new rows to tblBag_databak, but I'm getting a syntax error.

CREATE TRIGGER trgtblBag_databak ON [tblBag_data]
FOR INSERT
AS

If @.@.trancount > 0
insert tblBag_databak
--return
end

Thanks for your help.Why are you checking @.@.trancount?

What kind of insert is that?

You have an END with no BEGIN

How about...

CREATE TRIGGER trgtblBag_Data ON tblBag_data
FOR INSERT
AS

INSERT INTO tblBag_databak (supply column list here)
SELECT supply column list here
FROM inserted
GO

But why are you keeping duplicate data in 2 tables?

I track updates and deletes, and leave the "base" rows in the INSERT table...|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
Why are you checking @.@.trancount?

What kind of insert is that?

You have an END with no BEGIN

How about...

CREATE TRIGGER trgtblBag_Data ON tblBag_data
FOR INSERT
AS

INSERT INTO tblBag_databak (supply column list here)
SELECT supply column list here
FROM inserted
GO

Hi,

Thank you so much for your quick reply. It is working.
I need one table to show all the records and another used to be update.

Thanks again.|||Well it's funny...

I try to keep historical data, so I can track activity against a table...

I have on Current table, and 1 historical table...all the updates and deletes are dtored in the historical, and all the current (Inserts, and new updates) are stored in current...

Notice the addition of "administrative" columns to track when the event occured. And don't be to put off by the trigger...I'm using connection pooling and do stuff a little different with User Id's

Anyway, good luck

CREATE TABLE [dbo].[Company] (
[Company_Name] [char] (30) NOT NULL ,
[Active_Ind] [bit] NULL ,
[Psft_Company_Id] [char] (3) NULL ,
[FEIN] [char] (10) NULL ,
[Has_NonQual_Taxes] [bit] NULL ,
[Has_Qual_Taxes] [bit] NULL ,
[Created_By] [char] (8) NOT NULL ,
[Created_Ts] [datetime] NOT NULL ,
[Updated_By] [char] (8) NULL ,
[Updated_Ts] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company_H] (
[HIST_ADD_TS] [datetime] NOT NULL ,
[HIST_ADD_TYPE] [char] (1) NOT NULL ,
[HIST_ADD_BY] [char] (8) NOT NULL ,
[HIST_ADD_SYSUSER_BY] [char] (8) NOT NULL ,
[Company_Name] [char] (30) NOT NULL ,
[Active_Ind] [bit] NULL ,
[Psft_Company_Id] [char] (3) NULL ,
[FEIN] [char] (10) NULL ,
[Has_NonQual_Taxes] [bit] NULL ,
[Has_Qual_Taxes] [bit] NULL ,
[Created_By] [char] (8) NOT NULL ,
[Created_Ts] [datetime] NOT NULL ,
[Updated_By] [char] (8) NULL ,
[Updated_Ts] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Company_Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
CONSTRAINT [DF__Company__Created__619B8048] DEFAULT (getdate()) FOR [Created_Ts]
GO

ALTER TABLE [dbo].[Company_H] WITH NOCHECK ADD
CONSTRAINT [DF__Company_H__HIST___59C55456] DEFAULT (getdate()) FOR [HIST_ADD_TS]
GO

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END