Using WHERE Clause In Trigger Definition
Dec 3, 2007
I've got a table that gets updated with live-data constantly throughout the day. I've got to run some statistical analysis on the data in this table based off of the value of one of the columns. The column can be 4 values, but I'm only looking to analyze data for rows containing one of two of the 4 total values. IE
Table
|Status| more data....|
|1|......|
|2|......|
|3|......|
|4|......|
|1|......|
|4|......|
I want to only analyze the data of rows with 1 and 4 as values. I don't care about 2 or 3...
I was thinking of using a Trigger, but I'm not aware of a way to analyze the data that fires the Trigger.
Ideally, I'd like to do something like
CREATE TRIGGER analyze ON table AFTER INSERT WHERE column = '1' OR column = '4' AS 'Trigger sql here'
Is this possible?
View 13 Replies
ADVERTISEMENT
May 29, 2008
Such as check all triggers that assign value to some columns ?
Thank you very much.
View 1 Replies
View Related
Aug 17, 2007
Hello All
Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.
I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:
There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.
[stored procedure name] and [trigger name] are where the actual names appear in the message.
I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).
Has anyone seen this, and if so, how can I fix it?
Thanks in advance for your help!
Jay
View 4 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Oct 25, 2004
Could someone explain to me what the acroymn BCV means and how it is applied with an SQL database?
Thanks
View 4 Replies
View Related
Apr 2, 2007
In sql2k5 we have
GRANT VIEW DEFINITION ON sp_name TO user
Did this option present in SQL2K?
------------------------
I think, therefore I am - Rene Descartes
View 1 Replies
View Related
Mar 5, 2008
Which SP should I use to get the definition of a user created SP?
Thanks,
Prakash.P
View 3 Replies
View Related
May 18, 2004
It is possible to get the definition of an index in a script?
I want the "Create index..." string so I can drop and recreate the index in a single statement.
You can do it in the Query Analyzer object browzer with the "Script object..." options.
View 2 Replies
View Related
Mar 6, 2008
Hi,
I need a query to get table Definition in sql server 2005.
please help me out.
Raghu sunkara.
View 6 Replies
View Related
Apr 5, 2006
Hello, i would like to know if it's possible to generate automatically a word document or an excel document that will contain all the metadata definition, for example containing the source columns names, their datatype, and the destination with their datatypes, so that it would easy to create a data dictionnary .
Thank you in advance.
View 2 Replies
View Related
May 30, 2007
Good morning, all!
I'm tasked with creating POCO (C# Class Object) and XML definitions for three different kinds of records coming from my input file. The problem is that I'm not sure how to do this within the SSIS framework. It seems to me that I can somehow define a class and include XML element tags in the definition, but I'm not sure where to look/who to ask/how to do that!
The goal is to be able to assign the data types and class attributes for various parts of each input record based on position, and to serialize the data as XML (and therefore my inputs to my Data Flow will be XML objects instead of a flat file, as I had been doing it...).
Can anyone point me at a good tutorial? Or a simple example?
Also, is "DateTime" a recognized data type in C#?
Thanks a bunch!
Jim Work
View 6 Replies
View Related
Mar 19, 2007
Hello :
I want to use a style sheet, in a .RDL file, that is instead of having that:
<BackgroundColor>#336cad</BackgroundColor>
I want to put:
<BackgroundColor> class = « my color » </BackgroundColor>,
And if it is possible, how I make for connected the .RDL and the .CSS.
Thank's.
View 2 Replies
View Related
Nov 6, 2007
Someone know what table/column contain the rdl definition in the ssrs database?
thanks
View 1 Replies
View Related
Jul 20, 2007
Is it possible to control the number of table rows that can be displayed by modifying the report definition?
View 4 Replies
View Related
Sep 27, 2007
I have a 2005 .Net 2.0 solution that includes two projects, a windows project and a Reporting Services project. The report viewer is apart of the windows project and I am wanting to open reports in the RS project but keep getting a 'the report definition has not been specified' error.
I set the ReportPath to point to the physical path of the RS project but it does no good.
Anyone have any idea how to run a report that is not apart of the project?
Thanks.
Steve
View 1 Replies
View Related
Oct 5, 2006
Hello,I'm using Visual Studio 2005 with ADO.Net 2.0 but I am missing a definition for the sqlcommands' object "ExecuteRow"oCommand = new System.Data.SqlClient.SqlCommand();oCommand.ExecuteRow() ----------------------------> this is the missing definitionAny ideas anyone ???
View 4 Replies
View Related
Apr 7, 1999
We are trying to create a view that references lengths in both metres and feet.
What we want to do is to create a baselength column which either holds the value of the metres column or calculates the metric value of the feet column if there is no value in the metric column.
We can do all the maths for the calculations etc, it is just putting the IF statement into the view to test the values that we are struggling with.
Any help would be appreciated.
Thanks
View 2 Replies
View Related
Mar 12, 2004
I'm using the following to get table/column names:
select so.name, sc.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and so.type = 'U'
order by so.name, sc.colid
What is the equivalent for views?
Thanks
View 4 Replies
View Related
Aug 23, 2000
During work with creating, updating or deleting objects in a database,
we use explicit name of the objects, for example
DROP PROCEDURE myProc.
By what the method can I avoid using the explicit name and get something like this:
DROP PROCEDURE @myProc or DROP PROCEDURE id_myProc?
View 1 Replies
View Related
Apr 30, 2007
Plesae tell me the MSSQL Server equivalent of the below MySQL query .create table temp2(a varchar(23) comment 'male m');What is the use of specifying a keyword 'comment' in the column definition. Will it make any difference
View 14 Replies
View Related
May 8, 2008
I have set up a couple of views for a user but they want to be able to see the database table columns by doing right click on table and getting the columsn.
I tried to give the permissions to database by right clicking on database and doing the user and giving view defintion.
-- [TABLEA] contains no columns that can be inserted or the current user does not have permissions on that object.
User not allowed to select the data .
Thanks
View 2 Replies
View Related
Dec 1, 2006
Hosam writes "Dear Team
Im new to SQL server 2005, and I wonder if I can create the database documentation, I mean table schema and attributes as word or excel file.
Is there anyone can help me please
Thanks
Hosam"
View 1 Replies
View Related
Feb 7, 2008
Hi SQL Professionals ,
I have a requirement like this,
I have a Live Database as well as the Test database with the same Definition
now i need to write a SQL to check identity of these two database tables,
i mean i need to check if the Test Database has got the Same Table definition as Live Database table definition ?
In the same way how do i check for the Stored peocedures ?
how do i write a SQL for this ?
regardssuis
View 4 Replies
View Related
May 13, 2008
I want to create a view on the table "workorder" that contains a serial id "woid". Another table, "wo_comment" contains multiple comments for each "woid".
I would like to create a view view_workorder with a column "allcomments" that is basically a concatenation of all the comments within the table "comment".
Table: workorder has columns: woid, startdate (plus many others)
Table: wo_comment has columns: woid, comment, sequence
Desired View: view_workorder has columns: woid, startdate, allcomments
I tried to create a view as follows but get errors concerning the "CURSOR" statement making me wonder if I can use declarations and CURSORS within a view?
create view [workorder_view] as (
Select
w.woid,
w.startdate,
cast( DECLARE @all_comments nvarchar(4000),
@curr_comment nvarchar(256)
DECLARE wo_comment_cur CURSOR
FOR
SELECT woc.Comments
FROM WO_Comment as woc
WHERE woc.woid = w.woid
OPEN wo_comment_cur
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
WHILE (@@FETCH_STATUS = 0)
BEGIN
Set @all_comments = @all_comments + @curr_comment
FETCH NEXT FROM wo_comment_cur INTO
@curr_comment
END
CLOSE wo_comment_cur;
DEALLOCATE wo_comment_cur;
as nvarchar(80000)) as COMMENTS
from workorder AS w
Thanks for any help you can provide!
View 4 Replies
View Related
May 28, 2007
Hi there guys!!
I am having an issue with generating a table for an report on the fly. I have been referring to the example on www.gotreportviewer.com and their example works fine but when i attempt to do my own version (by adding the table, I get the following error:
The report definition is not valid. Details: The element 'TableColumns' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has incomplete content.
List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:TableColumn'.
From my understanding 'TableColumns' was not expected even though i reference it correctly in my code and I it is defined in the ReportDefinition. It also causes no problems for the example from the above website.
Can anyone help with this problem?
Thanks a million!
View 3 Replies
View Related
Nov 4, 2006
What is the definition of 'Persistent Data' as related to the definition of a RDBMS?
thx,
Kat
View 4 Replies
View Related
Dec 4, 2000
I need to keep a table indicating the number of "transactions" or Logical Units of Work (LUW) against each database each day. I know transactions are defined with a Begin and End statement but most of our software is from 3rd party vendors who don't use begin and end. How can I determine how many transactions (not necessarily I/O although that might be nice in the future) has occurred against each database. Books On Line does not address the question of transactions other than to say use the Begin/End statments. Any thoughts would be greatly appreciated.
Thanks in advance
Bill Bergen (1-302-636-6814)
View 1 Replies
View Related
Apr 23, 2014
Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; --
define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
View 8 Replies
View Related
Sep 8, 2014
I want to create a login with some restriction like the following...
1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....
View 3 Replies
View Related
Nov 19, 2014
I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.
View 1 Replies
View Related
Jul 23, 2005
We hear about catalog table in documentation but is this the same asdatabase schema?What is the definiation of catalog table?What does it pertain to?Thanks--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Apr 1, 2008
Hi,
When I am trying to create an indexed view using group and COUNT(*), it gave me the following error:
€œMsg 10136, Level 16, State 1, Line 2
Cannot create index on view "AdventureWorks.Sales.vOrders" because it uses the aggregate COUNT. Use COUNT_BIG instead.€?
When I refered the SQL Books Online, I found the following statement: €œIf GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING€?.
Though my question is basic, I am curious to know the difference between COUNT() and COUNT_BIG(). The only difference I knew is COUNT_BIG() returns bigint. If this is the only difference, why can€™t we use COUNT() in indexed view definition and why COUNT_BIG() is allowed?
Regards,
-Aazad.
View 5 Replies
View Related
