Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2002
    Posts
    395
    [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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •