MY SQL Trigger Edit Excute Query On Nothing Change

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MY SQL Trigger Edit Excute Query On Nothing Change

Author

Topic

kok_sheng2000
Starting Member

10 Posts

Posted - 2012-05-04 : 06:01:52

Hi, Everyone. I'm Very New to MY SQL Trigger. I would like to have
a trigger Edit for update another table on change of the field data.
Example : The Insert Into Table Names [MyLog] When a change of data Customer Name From AAA to BBB. The below is the code that i use in trigger. But My problem is, it will excute the Insert Statement even i not change the Customer name ... Please Help Me ... Thanks In advanced ...

==================================================================

USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_Customer_Edit]
ON [dbo].[Customer]
FOR UPDATE
AS
Declare @strMyAppName nvarchar(100)
Declare @bLink bit

BEGIN
Declare @Link_Key bigint
Declare @Point_Before nvarchar(10) ,@Point_After nvarchar(10)
Declare @Account_Before nvarchar(50),@Account_After nvarchar(50)

SET NOCOUNT ON;
IF Update(Cust_Name)
Insert Into Export (Exp_TableName,Exp_Action,Exp_Statement,Exp_Link_Key,EXP_PostDateTime,EXP_AppName)
Select 'Customer','EDIT','Cust_AcctCode = '''+ Cust_AcctCode + '''',Link_Key,GetDate(),@strMyAppName From Inserted where cust_acctcode <> 'CASH'

END

======================================================================

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-04 : 06:16:59

are you not using MS SQL Server right ?

SQLTeam.com is for MS SQL Server. for MySQL, try forums.mysql.com or dbforums.com

KH
[spoiler]Time is always against us[/spoiler]

kok_sheng2000
Starting Member

10 Posts

Posted - 2012-05-06 : 22:04:18

Hi, khtan. Yes I Using MS SQL Server. And another Add-on Question.
How I can Check All Fields Is any of the fields is Change. I Mean
One Table May have many Fields and any Query that can Check
All Fields in change data other than check it one field by one feild ?
Thanks ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-06 : 22:12:29

those variable are not used, you can removed it


ALTER TRIGGER [dbo].[T_Customer_Edit]
ON [dbo].[Customer]
FOR UPDATE
AS
Declare @strMyAppName nvarchar(100)
Declare @bLink bit

BEGIN
Declare @Link_Key bigint
Declare @Point_Before nvarchar(10) ,@Point_After nvarchar(10)
Declare @Account_Before nvarchar(50),@Account_After nvarchar(50)

SET NOCOUNT ON;
IF Update(Cust_Name)
Insert Into Export (Exp_TableName,Exp_Action,Exp_Statement,Exp_Link_Key,EXP_PostDateTime,EXP_AppName)
Select 	'Customer','EDIT','Cust_AcctCode = '''+ Cust_AcctCode + '''',Link_Key,GetDate(), @strMyAppNameapp_name()
From 	Inserted 
where 	cust_acctcode <> 'CASH'

END

refer to http://msdn.microsoft.com/en-us/library/ms187326.aspx
UPDATE() will return TRUE if the column is included in the update statement regardless of the value is changed or not.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-06 : 23:17:28

quote:
Originally posted by kok_sheng2000

Hi, khtan. Yes I Using MS SQL Server. And another Add-on Question.
How I can Check All Fields Is any of the fields is Change. I Mean
One Table May have many Fields and any Query that can Check
All Fields in change data other than check it one field by one feild ?
Thanks ...

http://msdn.microsoft.com/en-us/library/ms186329.aspx

KH
[spoiler]Time is always against us[/spoiler]

kok_sheng2000
Starting Member

10 Posts

Posted - 2012-05-07 : 01:32:17

Thanks again khtan, I Found that Columns_Updated will
update my log table even no any change from any field
. What is find is for Checking all Fields that have
really change. Thanks ...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 01:44:33

inner join to the deleted table and compare the column

KH
[spoiler]Time is always against us[/spoiler]

kok_sheng2000
Starting Member

10 Posts

Posted - 2012-05-07 : 02:05:50

Thanks for khtan, another problem faced ... ==||

WHILE @Counter < @ColumnCount
BEGIN
Set @Counter = @Counter + 1
SELECT @StringColumn = (SELECT COL_NAME(OBJECT_ID(@TableName), @Counter) AS 'Column_Name')
IF Update(@StringColumn)
-- Execute Some Code Here
END

With Code Above, I want to Loop every Columm in a Table and Check
update using IF Update(@StringColumn), but cant Execute, The message show
"Incorrect syntax near '@StringColumn'." .Please Help ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources