hy guys
i m trying to create trigger but i tired to get an error msg
trigger which i want to create is :
CREATE TRIGGER TR_TESTING ON EMPLOYEES
FOR INSERT
AS
DECLARE @TMP AS VARCHAR(1)
SET @TMP=SUBSTRING(FirstName,0,1) FROM EMPLOYEES
IF @TMP='A'
BEGIN
INSERT INTO EMPLOYEES(LastName,FirstName) VALUES('DHARANI','AAMIR')
END
ELSE
BEGIN
PRINT 'INSERT VALID RECORD'
END
Hi Guys, Is there any Wizard or software or tools that I can use to create Triggers for my tables ... !???? It is for MSSQL 2005 database Thanks for your help,Mehdi
Would it be possible to generate SQL Script using triggers or stored procedures whenever there is a change in database tables like UPDATE or APPEND. Whenever we update or append record it should create a script and save it in a text file so that we can update parent database only with those updated records.
For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.
We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.
The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
/************************************************************************************************************ Created By: Bryan Massey Created On: 3/11/2007 Comments: Stored proc performs the following actions: 1) Queries system tables to retrieve table schema for @TableName parameter 2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include additional history columns. 3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table. ******************************************* MODIFICATIONS ************************************************** MM/DD/YYYY - Modified By - Description of Changes ************************************************************************************************************/ CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger @TableName VARCHAR(200), @CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y" AS
-- query system tables to get table schema SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription, CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType, CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision, CONVERT(VARCHAR(10), SC.XScale) AS FieldScale, CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls FROM SysObjects SO INNER JOIN SysColumns SC ON SO.ID = SC.ID INNER JOIN SysTypes ST ON SC.xtype = ST.xtype LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0 WHERE SO.xtype = 'u' AND SO.Name = @TableName ORDER BY SO.[name], SC.ColOrder
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0 BEGIN
-- create list of table columns IF LEN(@FieldList) = 0 BEGIN SET @FieldList = @FieldName SET @FirstField = @FieldName END ELSE BEGIN SET @FieldList = @FieldList + ', ' + @FieldName END
IF LEN(@SQLTable) = 0 BEGIN SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF END
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY') BEGIN SET @SQLTable = @SQLTable + '(' + @FieldLength + ')' END ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC') BEGIN SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')' END
IF @AllowNulls = 'Y' BEGIN SET @SQLTable = @SQLTable + ' NULL' END ELSE BEGIN SET @SQLTable = @SQLTable + ' NOT NULL' END
SET @SQLTable = @SQLTable + ',' + @CRLF
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls END
CLOSE CurHistoryTable DEALLOCATE CurHistoryTable
-- finish history table script with standard history columns SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF SET @SQLTable = @SQLTable + ' )'
PRINT @SQLTable
-- execute sql script to create history table EXEC(@SQLTable)
IF @@ERROR <> 0 BEGIN PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************' RETURN -1 END
IF @CreateTrigger = 'Y' BEGIN -- create history trigger SET @SQLTrigger = '/************************************************************************************************************' + @CRLF SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
--PRINT @SQLTrigger
-- execute sql script to create update/delete trigger EXEC(@SQLTrigger)
IF @@ERROR <> 0 BEGIN PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************' RETURN -1 END
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
Hello All, I have to write Trigger for Update, I have two tables, one is for storing records of current values, and one is for storing history of values. How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values. Help me. General Problem
I need to create a set of rows every time a new row is inserted into a table. Example (I think this would work)... select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id) Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values? Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger? Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value. SET @ActionTaken = 'A' IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
Hi All, I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set (Lastname = @Lastname) where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR ('cannot change lastname', 16, 1) ROLLBACK TRAN RETURN END GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR insert,Update AS
IF left('Loan_No',3)='ABC' update Test_TRG set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF' update Test_TRG set loanbook = 'NEWDEF' where loanbook is null
else
update Test_TRG set loanbook =left('Loan_No',3) where loanbook is null
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ". The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if The left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=KVS and its null then NEWKVS left(loan_no,3)=MFS and its null then MFS left(loan_no,3)=TCR and its null then TCR left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it l get an error.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR INSERT, UPDATE AS
UPDATE Test_TRG SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG' WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS' WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS' WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 ) END FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK -- Fill in Primary Key or other Join Column(s) WHERE LEFT( i.Loan_No, 3 ) = 'MCG' OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL ) OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'. Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'.
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...
I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:
CREATE Trigger upd_cost_master On tbl_cost_master For Insert, Update As
To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?
I'm using SQL Server 7.0. I have an insert trigger on table t05r. I populate table t05r with a DTS package. The trigger is supposed to add the record which was inserted into t05r, into t39r. After I run the DTS package to populate t05r, I checked t39r and no records where added. What is going wrong? Is it that DTS uses bulk copy and triggers don't fire on bulk copy?
I have an insert trigger on table A which inserts records into table B. This insertion into B fails( or may fail ) at certain conditions. I do not want the insertion into A to be affected because of this. Any ways to achieve this ?
I've update triggers on my table, But for some of the transactions, I don't want the triggers to fire. I know in Sybase there are set triggers off. In MS SQL, Is there any similar commands which will set triggers off for particular sql statements.
Hi! I am having trouble with setting triggers while doing my project. The project is on ATM machine driven through SQL. So I have Date of Transaction in one table and Account Expiry in another.
I want to set the trigger to check Account expiry when doing transaction so i wrote T-SQL
select........ getDate()-Dateoftransaction=expiry begin '...' end
the system saying I have got an error near the word 'begin'.
I am desperately needing help. running out of ideas.
