trigger on select
in [Server Configuration]
|
From: Andrea Caldarone on 25 Nov 2009 05:12 Hi all, I know that "trigger on select" does not exists, I'm searching something to accomplish this need: I have a table, let's say: [Some_table] [ID] int primary key [Flag] bit [Some_Field] nvarchar(40) when a user (all user) does a select on a row of that table that has [Flag]=1 the select statement does not returns the correct value for [Some_Field] column but a specific value that I specify in the "trigger on selest". How can I do this?
From: Uri Dimant on 25 Nov 2009 06:40 This script was written by Dejan Sarka long times ago and since you have not provided us with the version of SQL Server you are using I copied/pasted his example works on SQL Server 2000 (have not tested on lastest versions) For example, let's say we want to follow selects on the Customers table of the Northwind database. Create a trace with only the following settings: - SP:StmtCompleted and SQL: StmtCompleted events - EventClass, TextData, ApplicationName and SPID columns - DatabaseID Equals 6 (DB_ID() of the Northwind database) and TextData Like select%customers% filters - Name the trace SelectTrigger and save it to a table with the same name in the Northwind database. Start the trace, and create the following trigger using Query Analyzer: CREATE TRIGGER TraceSelectTrigger ON SelectTrigger FOR INSERT AS EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!', warning Now check how trigger works by performing couple of selects: SELECT TOP 1 * FROM Customers SELECT TOP 1 * FROM Orders SELECT TOP 1 c.CustomerID FROM Customers c INNER JOIN Orders o ON c.CustomerID=o.CustomerID With Event Viewer, check whether you got two warnings in the Application log for the 1st and the 3rd queries (the 2nd should be filtered out). "Andrea Caldarone" <andrea.caldarone(a)poste.it> wrote in message news:%23Bhl7ebbKHA.1648(a)TK2MSFTNGP05.phx.gbl... > Hi all, > > I know that "trigger on select" does not exists, I'm searching something > to > accomplish this need: > > I have a table, let's say: > [Some_table] > [ID] int primary key > [Flag] bit > [Some_Field] nvarchar(40) > > when a user (all user) does a select on a row of that table that has > [Flag]=1 the select statement does not returns the correct value for > [Some_Field] column but a specific value that I specify in the "trigger on > selest". > > How can I do this? >
From: Andrea Caldarone on 25 Nov 2009 08:46 "Uri Dimant" <urid(a)iscar.co.il> ha scritto nel messaggio news:%23sr5kPcbKHA.1592(a)TK2MSFTNGP06.phx.gbl... > This script was written by Dejan Sarka long times ago and since you have > not provided us with the version of SQL Server you are using I > copied/pasted his example works on SQL Server 2000 (have not tested on > lastest versions) > > > For example, let's say we want to follow selects on the Customers table of > the Northwind database. Create a trace with only the following settings: > > - SP:StmtCompleted and SQL: StmtCompleted events > > - EventClass, TextData, ApplicationName and SPID columns > > - DatabaseID Equals 6 (DB_ID() of the Northwind database) and > TextData Like select%customers% filters > > - Name the trace SelectTrigger and save it to a table with the > same name in the Northwind database. > > Start the trace, and create the following trigger using Query Analyzer: > > > > CREATE TRIGGER TraceSelectTrigger ON SelectTrigger > > FOR INSERT > > AS > > EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!', > warning > > > > Now check how trigger works by performing couple of selects: > > > > SELECT TOP 1 * > > FROM Customers > > SELECT TOP 1 * > > FROM Orders > > SELECT TOP 1 c.CustomerID > > FROM Customers c INNER JOIN Orders o > > ON c.CustomerID=o.CustomerID > > > > With Event Viewer, check whether you got two warnings in the Application > log for the 1st and the 3rd queries (the 2nd should be filtered out). > > You are right, I've forgot to mention the MSSQL version, it is SQL Server 2005 SP3. What I want is not to monitor access on a certain table, I want to dynamically modify the data presented to the user like this: CREATE TRIGGER trg_Example ON Some_table BEFORE SELECT AS BEGIN IF (Flag= 1) [a certain column]='some value I decide' END So this is the table [Some_table] [ID] int primary key [Flag] bit [Some_Field] nvarchar(40) [ID] [Flag] [Some_Fiels] 1 0 a 2 0 b 3 1 c if i do: SELECT * FROM [Some_table] WHERE [ID]=3 I want to get [ID] [Flag] [Some_Fiels] 3 0 some value I decide and if I do: SELECT * FROM [Some_table] WHERE [ID]=1 I want to get the data without any chage: [ID] [Flag] [Some_Fiels] 1 0 a
From: Uri Dimant on 25 Nov 2009 09:07 Andrea The only option I aware of is using a trace and filter out for WHERE cond and if you specify ID column as a parameter you won't see the actual value "Andrea Caldarone" <andrea.caldarone(a)poste.it> wrote in message news:evjCyWdbKHA.1028(a)TK2MSFTNGP06.phx.gbl... > > "Uri Dimant" <urid(a)iscar.co.il> ha scritto nel messaggio > news:%23sr5kPcbKHA.1592(a)TK2MSFTNGP06.phx.gbl... >> This script was written by Dejan Sarka long times ago and since you have >> not provided us with the version of SQL Server you are using I >> copied/pasted his example works on SQL Server 2000 (have not tested on >> lastest versions) >> >> >> For example, let's say we want to follow selects on the Customers table >> of the Northwind database. Create a trace with only the following >> settings: >> >> - SP:StmtCompleted and SQL: StmtCompleted events >> >> - EventClass, TextData, ApplicationName and SPID columns >> >> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and >> TextData Like select%customers% filters >> >> - Name the trace SelectTrigger and save it to a table with the >> same name in the Northwind database. >> >> Start the trace, and create the following trigger using Query Analyzer: >> >> >> >> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger >> >> FOR INSERT >> >> AS >> >> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!', >> warning >> >> >> >> Now check how trigger works by performing couple of selects: >> >> >> >> SELECT TOP 1 * >> >> FROM Customers >> >> SELECT TOP 1 * >> >> FROM Orders >> >> SELECT TOP 1 c.CustomerID >> >> FROM Customers c INNER JOIN Orders o >> >> ON c.CustomerID=o.CustomerID >> >> >> >> With Event Viewer, check whether you got two warnings in the Application >> log for the 1st and the 3rd queries (the 2nd should be filtered out). >> >> > > You are right, I've forgot to mention the MSSQL version, it is SQL Server > 2005 SP3. > > What I want is not to monitor access on a certain table, I want to > dynamically modify the data presented to the user like this: > > CREATE TRIGGER trg_Example ON Some_table BEFORE SELECT > AS > BEGIN > IF (Flag= 1) [a certain column]='some value I decide' > END > > So this is the table > [Some_table] > [ID] int primary key > [Flag] bit > [Some_Field] nvarchar(40) > > [ID] [Flag] [Some_Fiels] > 1 0 a > 2 0 b > 3 1 c > > if i do: SELECT * FROM [Some_table] WHERE [ID]=3 I want to get > > [ID] [Flag] [Some_Fiels] > 3 0 some value I decide > > and if I do: SELECT * FROM [Some_table] WHERE [ID]=1 I want to get the > data without any chage: > [ID] [Flag] [Some_Fiels] > 1 0 a > > >
From: Scott Morris on 25 Nov 2009 10:00
Use a view. There isn't any mechanism within sql server to intercept a select statement or its resultset and alter either. If your reason for asking involves an application that you cannot control, you might be able to work around that by changing the name of the existing table and creating a view to act as its substitute. Another approach is to use a stored procedure. Still yet another approach is to simply change the query to generate the resultset you desire - if that is possible. |