.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Trigger problem

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
Hi, I am new to sql server,we have a stored procedure it creates automatic trigger for update,insert,delete operations for all tables in our database.Everytime when a record is updated in a table the description(Master Audit table Message) is stored in an audit table saying Column name:1 to 2 and the tablename ,action is also recorded in the audit table.But we need more informative messages like State:texas to california instead of StateId: 2 t0 1.I used extended properties and i wrote a stored procedure GetDisplayValuename.For example in a table extended properties i gave Audit_IDColumn = ID,Audit_ValueColumn = TypeName,Audit_Name = Blood Type,the table name is tbl_BloodType. ALTER PROCEDURE dbo.GetDisplayValuename  @TableName SYSNAME, @ID BIGINT, @Result NVARCHAR(1000) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @IDColumn SQL_VARIANT DECLARE @ValueColumn SQL_VARIANT DECLARE @IDColumnName NVARCHAR(1000) DECLARE @ValueColumnName NVARCHAR(1000) DECLARE @Query NVARCHAR(1000) DECLARE @Value NVARCHAR(1000) DECLARE @PARAMDEF NVARCHAR(500) DECLARE @strID NVARCHAR(1000) SET @Query = N'SELECT @IDColumnOUT = value FROM fn_listextendedproperty('Audit_IDColumn', 'SCHEMA', 'dbo', 'TABLE', '' + @TableName + '', default, default);' SET @PARAMDEF = N'@IDColumnOUT SQL_VARIANT OUTPUT'; EXECUTE sp_executesql @Query, @PARAMDEF, @IDColumnOUT = @

View Complete Post

More Related Resource Links

Problem With Using Trigger for Setting Label Content

Basically, I have a label and depending on the Object type of the current data source item I want to control what the content of the label is. The trigger I am trying is: <Label Margin="222,21,242,134" Name="MyLabel"> <Label.Resources> <DataTrigger Binding="{Binding Path=/EntryType}" Value="Object.Type1" x:Key="myTrigger"> <Setter TargetName="MyLabel" Property="Content" Value="{Binding Path=/Object.Property1}"/> </DataTrigger> </Label.Resources> </Label> The problem is that I am getting this error: 'Content' member is not valid because it does not have a qualifying type.   Do I need to use some sort of converter or something? Or am I aproaching this worng?

Logon Trigger blocking problem

I'm trying to learn how to use microsoft sync framework using the following example, but am having a problem with a logon trigger blocking something.  If I disable the trigger it will work, but that really isn't an option.  Normally, if something is blocked it is placed in a blockedLog table with the name of the user and application.  If we need to allow access then we can put this information in the BlockedExceptions table to allow it through.  Initially, the .Net SqlClient Data Provider gets blocked.  After adding it to the accepts table, I still get a blocking error due to the trigger, but I can't determine what is actually being blocked.  The table never fills with any information about what gets blocked after fixing the initial .Net SqlClient Data Provider error and I have not been able to figure out anything using the profiler or activity monitor. Sync Example in question:http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=3422 CREATE TRIGGER [BlockLogins] ON ALL SERVER WITH EXECUTE AS 'DBTrigger' FOR LOGON AS BEGIN DECLARE @Allow bit SET @Allow = 'TRUE' IF (SELECT COUNT(*) FROM DB.dbo.Programs WHERE AppTitle= APP_NAME()) = 0 BEGIN -- ** Other allowed apps and users are in BlockedExceptions ** -- If it is a non-application that any user is allowed to run then let it through.

Trigger problem

Hi i have 2 trigger first trigger is on table member.It makes when member tables insert or update it insert record to forum_member.And second trigger on forum_member it makes when insert or update insert record to member.And it Goes into an endless loop. How can i solve this problem

Problem With Creating Trigger For ToggleButton


Hi everyone

I have created an expander control template.  The idea is that when the togglebutton contained within the expander is checked (clicked first time), the arrow and arrow2 object should have their data property changed.  I am having trouble in creating the triggers.  

Can someone please assist me as to where the triggers should be created.

The xaml code for the triggers are:


<Trigger Property="IsChecked" Value="True">
            <Setter TargetName="arrow" Property="

sql trigger problem??


I have created a trigger for insert and whenevr i insert a record it must notify or insert the values in my log table,i want to know how we know trigger is fired?? please help me with this?

my trigger for insert

create trigger insert2
on emp
for insert
declare @sno int,@result varchar(50)
select @sno=sno,@result=@result from insert1
insert into triglog values (@sno,@result)

but i am getting this error An explicit value for the identity column in table 'triglog' can only be specified when a column list is used and IDENTITY_INSERT is ON.help me with this? thanks.

trigger problem inserting null values??


I have created a trigger for insert and whenever i insert the data the trigger should fire and notify some value inserted i want to create log table to save this information,now i have created a trigger

USE [mine]
/****** Object: Trigger [dbo].[insert2]  Script Date: 10/03/2010 15:00:21 ******/
ALTER trigger [dbo].[insert2] 
  on [dbo].[emp] 
  after insert 
  declare @sno int,@result varchar(50),@empid int,@name varchar(50) 
select @empid=empid,@name=@name from emp  
 insert into triglog values(@empid,@name)  
but when

Trigger Problem


If i have a single statement trigger. Being actived by a multiple insert statement. How to go about resolve it.

[Subscribe Trigger]

-- Update blockee in list will delete users contained in list
-- Appendix C Point3
ALTER TRIGGER [dbo].[add_follow_when_scrib]
ON [dbo].[Subscribe]

DECLARE @subscriber VARCHAR(20)
DECLARE @list int
SELECT @subscriber = (SELECT Username FROM Inserted)
SELECT @list = (SELECT ListID FROM Inserted)

WHERE L.ListID = @list)

PRINT 'Inserting list of users in list '/*+@list*/+' for '+@subscriber+' to follow.'

INSERT INTO Follow (Followee, Follower)
    (SELECT C2.Username AS Followee,@subscriber AS Follower
        WHERE L2.ListID = @list
            AND L2.ListID = C2.ListID
            SELECT *
            FROM Follow

Problem concerning a trigger on a trace table


Hello everyone,

I want certain steps to be taken in response to a user logout event, so I set up a trace in SQL Profiler that writes to a database table. On that table I created a trigger that fires ON INSERT.This works fine with SQL Server 2000, but with SQL Server 2008 the trace keeps crashing as soon as the first logout event occurs, i.e. the first row is inserted into the trace table.

Any ideas why? I would also be happy about a hint to another way of capturing and reacting to the logout event.

Many thanks in advance,

Problem to group radio button across gridview rows

In general no one can easily group or make a single selection from radio button list inside gridview rows. There are a lot of way to make a single or unique selection from list. Here i want to share how i can address this problem in the most easiest way. Googling the problem most of the cases i saw the grouping in horizontal way. Thats why i tried to group the radio buttons in vertical manner. Let i have a requirement like below:

Regrading Sharpoint 2010 problem and solutions

We can write your question and problem on this blogger.you get ans.use.

This sharpoint 2010 problem solving threads

Hi friend,

you can use this thread to solving problem and solutions.

Virtual Path Problem


 I get the following error on last line below (working on remote host): 'E:\web\sfcascom\ls_admin/uploads/images/001.JPG' is not a valid virtual path,

Note that the slashes go in different directions

I've tried a number of things but nothing seems to work. Thanks-


Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.Web.UI.WebControls

Imports System.Drawing.Imaging.ImageCodecInfo
Partial Class Protected_imageupload
    Inherits System.Web.UI.Page
    Private Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        ' Initialize variables
        Dim sSavePath As String
        Dim sThumbExtension As String
        Dim intThumbWidth As Integer
        Dim intThumbHeight As Integer
        Dim sFilename As String = ""
        ' Set constant values
        'sSavePath = "images/"
        sSavePath = Server.MapPath("~/") + "ls_admin/uploads/images/"

        sThumbExtension = "_thumb"
        intThumbWidth = 160
        intThumbHeight = 120

        ' If file field isn?t empty
        If FilUpload.PostedFile IsNot Nothing Then
            ' Check file size (mustn?t be 0)
            Dim myFile As Http

Strange Photo upload problem


I'm working project it's live on shared hosting

we'er uploading photos to a file on the server everything works fine the photo is resized renamed and sent to the folder

the upload page returns the thumbnail and the full size photo it's there all the photos have to be approved by an admin

twice now after they have been approved they disapear I checked the folder they are not there could there be anywhere

on the server they could get hung up before they go into the folder I have it setup so when you delete the data base entry it deletes the photo from the folder othe than that you need to open the folder and delete the photo this has hapened twice now I tried to recreate it with no luck

thaks for your help in advance

ASP.Net Caching problem ??


I'm having a problem with an asp.net 3.0 application using a gridview to display data.  The problem seems to be that despite a brand new .selectCommand being sent to the sqlDataSource, the gridview starts displaying data from *someone else's* query.  For example, user A is logged in and will be working fine until a few page submissions later when the gridview data starts suddenly showing information that belongs to another user. 

This is my first asp.net application, though I've been working with classic asp and ADO for years so I'm used to sending a SQL string and getting back the results from that string.  Apparently there is a complex caching system which is interferring which I do not at all understand so at this point I just want to stop the server from caching any data whatsoever.  Is this possible?

Any and all help will be much appreciated!!

Could some one tell me whats the Problem with this String Manipulation ??? C#.net



I store some values from code behind to a label which is Hidden !

<asp:Label ID="lbl_Hidden" runat="server" Visible = "false"></asp:Label>

and at the page load event i retrieve the value of the label & assign it to a string !

stringvalue = lbl_Hidden.Text;

Now i dont know why this manipulation does'nt work !

I've a character 'A' in the string and i'm unable to replace it with a space !

stringvalue.Replace(A, " ");

Wot do u think abt it ????

RaiseEvent problem


I have a parent page ProdView.aspx and two static UserControls, UC1 and UC2. Each UC is in an Ajax accordion pane and the accordion inside an updatepanel.

UC1 has a Gridview footer with couple of textboxes and a link button that fires an Add Row insert procedure. I want to Raise an event that will rebind the UC2 after the row insert in UC1.

First I created the sub delegate and event in UC1:

  • Public Delegate Sub MyRefresh(ByVal sender As Object, ByVal e As CommandEventArgs)
  • Public Event Refresh_UC1 As MyRefresh

In the same UC1 the Insert procedure that fires the insert and RaiseEvent:

Sub PricingGrid_Insert(ByVal sender As System.Object, ByVal e As EventArgs)
       Dim myConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
       Dim myCom As SqlCommand = New SqlCommand("AddRow", myConn)
       myCom.CommandText = "AddRow"
       myCom.CommandType = CommandType.StoredProcedure


Gridview Enable Selection Problem



I have a gridview with Enable selection and Enable Paging equals to TRUE.

I also set the PageSize of my gridview to 4 so that I only displays 4 rows per page.

The problem is when I click the pager in the bottom of my gridview, my gridview is disappearing and no records appear.

Here is code behind of my gridview:

If e.Row.RowType = DataControlRowType.DataRow Then
                e.Row.Attributes("style") = "cursor:pointer"

                e.Row.Cells(2).Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" & e.Row.RowIndex.ToString))
                e.Row.Cells(3).Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" & e.Row.RowIndex.ToString))
                e.Row.Cells(4).Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" & e.Row.RowIndex.ToString))
                e.Row.Cells(5).Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" & e.Row.RowIndex.ToString))
                e.Row.Cells(6).Attributes.Add("onclick", Page.ClientScript.GetPostBackEventReference(sender, "Select$" & e.Row.RowIndex.ToString))
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend