September 6, 2010 at 10:35 pm
Hi All,
Iam facing the problem, which i described below, its for auditing purpose.
Audit Deatils Will Be Stored in a table named, Audit_Detail, with the following coloumns
1. Tname
2. Flag -> 1 indicates, this coloumn hav to monitored, 0 means no need
3. Coloumn name of the table
I had written a AFTER INSERT TRIGGER
As follows
ALTER TRIGGER [dbo].[INSERTTRIGGER] ON [dbo].[OpportunityBase]
AFTER INSERT
AS
BEGIN
Declare @Typeas Varchar(50)
Declare @DispNameas varchar(50)
Declare @TNameas Varchar(50)
Declare @ocas Varchar(50)
Declare @Old_Valas varchar(500)
Declare @New_Valas varchar(500)
Declare @Queryas varchar(500)
Declare @GUID as uniqueidentifier
Declare @Modidate as datetime
Declare @UID as uniqueidentifier
Declare @ParamDef as nvarchar(1000)
Declare @rtnVal as nvarchar(1000)
Declare Row_Fetch cursor for
SELECT Type,DisplayName,TableName,OC
FROM Audit_Detail
WHERE Flag =1 and TableName = 'OpportunityBase'
--INSERT INTO UserTrigger (Name,ModiDate,oppid,flag) values (@opp_name,@EstiCloseValue , @Opp_id,'I')
--Open the cursor
Open Row_Fetch
Fetch Next From Row_Fetch into @Type, @DispName, @TName, @oc
WHILE @@Fetch_Status=0
BEGIN
--Set @Old_Val = (Select @oc from deleted)
Set @Query = 'SELECT @rtnVal ='+@oc+' From inserted '
Select @ParamDef = '@rtnVal nvarchar(1000) OUTPUT'
EXEC dbo.sp_executesql @oc, @ParamDef, @rtnVal OUTPUT
Set @GUID = (SELECT opportunityid FROM INSERTED)
Set@ModiDate = getdate()
Set @UId = (SELECT ModifiedBy From inserted)
Insert into Audit_Info(Entity,DisplayName,GUID,FieldName,Old_Value,New_Value,Flag,User_Id,Date)
Values (@Type, @DispName, @GUID, @oc, '', @rtnVal,'I',@UId,@Modidate)
Fetch Next From Row_Fetch into @Type, @DispName, @TName, @oc
END
END
So wat i did here is, when ever the row is inserted, using the cursor i ll get all the coloumn names which is gonna be monitored,
** Set @GUID = (SELECT opportunityid FROM INSERTED) this is working fine
** Set @Old_Val = (Select @oc from deleted) tis IS NOT WORKING. So i tried with the dynamic sql also, still i cant able to FETCH THE VALUE INSERTED . Sp plz help me. iam n deadline now. Plz help me out
September 6, 2010 at 10:45 pm
Since you are doing only an INSERT, there will be values only in the inserted table and there will be no values in the deleted table. deleted table will have values only when you UPDATE or DELETE some rows in the table.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 6, 2010 at 10:58 pm
yA MY DEAR FRND I KNOW THAT 🙂 🙂 bUT I COMMENTED THOSE LINES. sRY FOR POSTING INCLUDING MY COMMENTED LINES
September 6, 2010 at 11:04 pm
subbusa2050 (9/6/2010)
** Set @Old_Val = (Select @oc from deleted) tis IS NOT WORKING.
How would you get data from deleted table in case of INSERT trigger. there will be NO deleted table.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 6, 2010 at 11:16 pm
subbusa2050 (9/6/2010)
yA MY DEAR FRND I KNOW THAT 🙂 🙂 bUT I COMMENTED THOSE LINES. sRY FOR POSTING INCLUDING MY COMMENTED LINES
You have defined a trigger on table OpportunityBase for an INSERT with the following code
ALTER TRIGGER [dbo].[INSERTTRIGGER] ON [dbo].[OpportunityBase]
AFTER INSERT
So this will be fired only when you do some INSERT on the table OpportunityBase and not on an UPDATE or a DELETE.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 6, 2010 at 11:19 pm
subbusa2050 (9/6/2010)
Ok ok fine. but im not getting the value for the following queryset sqlqry = 'select' + @oc +'from inserted'
exec(sqlqry)
'select' + @oc +'from inserted' will not work here , you need to use select oc from inserted
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 6, 2010 at 11:21 pm
Ya ofcourse, as i said i need to fire the trigger wthen the opportunitybase table is inserted. So wats ur point ? ?
I need to get the INSERTED VALUES for different coloumn names(which is obtained from the audit table) . So give me some ideas for this issue alone. Other than all my ogic is correct. may be in hurry i might hav typed it wrongly. Plz guys, help me out
September 6, 2010 at 11:52 pm
subbusa2050 (9/6/2010)
Ya ofcourse, as i said i need to fire the trigger wthen the opportunitybase table is inserted. So wats ur point ? ?I need to get the INSERTED VALUES for different coloumn names(which is obtained from the audit table) . So give me some ideas for this issue alone. Other than all my ogic is correct. may be in hurry i might hav typed it wrongly. Plz guys, help me out
Your query below will return 10 rows if you INSERT 10 rows at a time.
set sqlqry = 'select' + @oc +'from inserted'
exec(sqlqry)
You are saying it does not return anything. Just check if the value of @oc is NULL. If not, then try debugging using set sqlqry = 'select' + @oc +'from inserted'
PRINT(sqlqry)
And if you try to assign it to some variable like say @rtn_val, you will get any 1 out of the 10 values( it need not be the 10th value you inserted always ).
You are also sure that your logic is correct. With all due respect i would say that I seriously doubt it. Even if you are in a hurry, i would not suggest you to rush.
I am not so good at cursors as i have rarely used them. Hence i can't give you any good suggestions on the same. But i think what you are trying to do can also be done in a set based manner. But that requires time which you don't seem to be having.
So, all i can say is Good Luck with Debugging. I hope you get a solution soon.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 6, 2010 at 11:53 pm
Bhuvnesh (9/6/2010)
subbusa2050 (9/6/2010)
Ok ok fine. but im not getting the value for the following queryset sqlqry = 'select' + @oc +'from inserted'
exec(sqlqry)
'select' + @oc +'from inserted' will not work here , you need to use select oc from inserted
I tried SELECT OC FROM INSERTED. But it doesnt work. Bcoz, the system will consider 'OC' as a coloumn name, but in our case, OC is a variable holding the coloumn names.
September 6, 2010 at 11:56 pm
Kingston Dhasian (9/6/2010)
subbusa2050 (9/6/2010)
Ya ofcourse, as i said i need to fire the trigger wthen the opportunitybase table is inserted. So wats ur point ? ?I need to get the INSERTED VALUES for different coloumn names(which is obtained from the audit table) . So give me some ideas for this issue alone. Other than all my ogic is correct. may be in hurry i might hav typed it wrongly. Plz guys, help me out
Your query below will return 10 rows if you INSERT 10 rows at a time.
set sqlqry = 'select' + @oc +'from inserted'exec(sqlqry)
You are saying it does not return anything. Just check if the value of @oc is NULL. If not, then try debugging using
set sqlqry = 'select' + @oc +'from inserted'PRINT(sqlqry)
And if you try to assign it to some variable like say @rtn_val, you will get any 1 out of the 10 values( it need not be the 10th value you inserted always ).
You are also sure that your logic is correct. With all due respect i would say that I seriously doubt it. Even if you are in a hurry, i would not suggest you to rush.
I am not so good at cursors as i have rarely used them. Hence i can't give you any good suggestions on the same. But i think what you are trying to do can also be done in a set based manner. But that requires time which you don't seem to be having.
So, all i can say is Good Luck with Debugging. I hope you get a solution soon.
Ya frnd thanks for your wishes. I am working in MS CRM as a front end. In this, at that time only ONE record will get inserted 🙂 🙂 I tried with sample input, it printed the @retval , but i cant use that values while inserting . Thats the problem 🙂 For past 8hrs i struck with this. And tryin lot of tinks to sort of the issue.
September 7, 2010 at 12:46 am
Bhuvnesh (9/6/2010)
subbusa2050 (9/6/2010)
** Set @Old_Val = (Select @oc from deleted) tis IS NOT WORKING.How would you get data from deleted table in case of INSERT trigger. there will be NO deleted table.
The inserted and deleted tables are always present (both of them) in a trigger regardless of the operation. In the case of an insert, the deleted table will simply be empty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2010 at 12:49 am
GilaMonster (9/7/2010)
Bhuvnesh (9/6/2010)
subbusa2050 (9/6/2010)
** Set @Old_Val = (Select @oc from deleted) tis IS NOT WORKING.How would you get data from deleted table in case of INSERT trigger. there will be NO deleted table.
The inserted and deleted tables are always present (both of them) in a trigger regardless of the operation. In the case of an insert, the deleted table will simply be empty.
thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 7, 2010 at 2:40 am
Hi Frnds,
Thansx For All Ur Support
SELECT * INTO #insertedval FROM INSERTED
create table #temp (value varchar(100))
declare @heat varchar(100)
select @sql = 'insert into #temp select ' +@OC+ ' from #insertedval'
exec (@sql)
select @new_val = (select top 1 * from #temp)
drop table #temp
--select @new_val=(select name from #inserted)
drop table #insertedval
Final i found out the solution
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply