Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: How to restore default trigger template

    I modified the trigger template in SQL Server Enterprise Manager.
    How to restore default trigger template,like follow
    --------------------------------------------------------
    CREATE TRIGGER [TRIGGER NAME] ON [dbo].[tablename]
    FOR INSERT, UPDATE, DELETE
    AS
    --------------------------------------------------
    Does the information of trigger template save in a file ?
    Anyone has idea ?

  2. #2
    Join Date
    Jan 2004
    Posts
    10

    Cool

    Overwrite "your changes" with the "default template text" that you want.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Look for a folder in SQL Server on your client drive called template...you can browse to find it....

    THen save any code as a *.tql file...

    I find the originals marginally ok...

    You can add your own...

    like:

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<stored_procedure_name, sysname, usp_New>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[<stored_procedure_name, sysname, usp_New>]
    GO
    
    CREATE PROCEDURE <stored_procedure_name, sysname, usp_New>
    AS 
    --
    --                	Enterprise Solutions
    --
    --           File:      <location_of_script, varchar(255), C:\TEMP\>
    --           Date:      <Creation_Date, Date, GetDate()>
    --         Author:      <author_name, varchar(255), Brett Kaiser>
    --         Server:      <server_name, varchar(255), NJROS1D151\NJROS1D151DEV>
    --       Database:      <db_Name, varchar(255), TaxReconDB>
    --          Login:      <Login, varchar(255), sa>
    --    Description:      <short_desc, varchar(255), This Procedure will >
    --
    --
    --                      The stream will do the following:
    --
    --				<Functions, varchar(255), '1. Function...>
    
    --
    --    Tables Used:	<Tables_Used, varchar(255), Table>
    --
    -- Tables Created:      <Tables_Created, varchar(255), Table>
    --
    --
    --  Row Estimates:
    --  		name                 		rows        reserved           data               index_size         unused
    --              -------------------- 		----------- ------------------ ------------------ ------------------ ------------------
    --
    --
    -- sp_spaceused tblAcct_LedgerBalance
    
    --Change Log
    --
    -- UserId   	Date        	Description
    -- -----------  --------------  ------------------------------------------------------------------------------------------
    -- <Author_Id, Varchar(8), x002548>  	<Install_Date, DateTime, GetDate> <Init_Rel, varchar(255), 1. Initial release>
    --
    --
    --
    
    Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int
    
    BEGIN TRAN
    
    	<SQL_Statement, varchar(255), SELECT>
    
    	Select @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    	If @error_out <> 0
    		BEGIN
    			Select @Error_Loc = 1
    			Select @Error_Type = 50001
    			GOTO <stored_procedure_name, sysname, usp_New>_Error
    		END
    
    	If @Result_Count = 0	-- Business Logic Error...This one for an expected row but no results
    		BEGIN	
    			Select @Error_Loc = 1
    			Select @Error_Message =  'Put appropriate message here'
    			Select @Error_Type = 50002
    			GOTO <stored_procedure_name, sysname, usp_New>_Error
    		END
    
    COMMIT TRAN
    
    <stored_procedure_name, sysname, usp_New>_Exit:
    
    -- Place any house keeping procedures here like...
    
    Set ansi_warnings ON
    
    return
    
    <stored_procedure_name, sysname, usp_New>_Error:
    
    Rollback TRAN
    
    If @Error_Type = 50001
    	BEGIN
    		Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))  
    					     + ',"' + '  @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
    					     + ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
    					     + ',"' + '  Message: ' + ',"' + RTrim(description)
    				  	   From master..sysmessages
    				 	  Where error = @error_out)
    	END
    If @Error_Type = 50002
    
    	BEGIN
    		Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
    			                + ',"' + ' Severity:  UserLevel ' 
    				          + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
    	END
    
    RAISERROR @Error_Type @Error_Message
    
    GOTO <stored_procedure_name, sysname, usp_New>_Exit
    
    GO
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    14
    Thanks your reply.
    I only find the fold "\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\Create Trigger",and find a file 'Create Trigger Basic Template.tql".
    but it is the template for Query Analyzer ,not for Enterprise Manager.Why?

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    In Enterprise Manager, you need to create a new procedure. Paste your code in. Then hit: Save as Template. Next time you pull up the create new procedure, this will be your template.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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