i had a view in my project, i am inserting a record in to that view(View contains a identity key for a column), with in stored procedure i am inserting a record i am not passing the identity key value to the insert statement. The record is getting inserted,Based on the identity key(i am getting the identity key value with Scope_Identity()) and with that value i am inserting records into another two tables.In this scenario every thing is working fine.
but now i am trying to place a trigger(instead of insert trigger) on the view, when i placed,it is not inserting record into first table,so i am not able to get the identity value of that record and process failed.
I have a strange problem, when I define an after update trigger on a view I have a message saying: "Server: Msg 208, Level 16, State 4, Procedure after_update_tblpcl_AnalogValue_Minutely, Line 1" although I am the owner of the view and its in my DB, but when I make the trigger as "Instead of Update" it works fine.
Is there an automated way of regenerating the stored procedures, views, and triggers? Currently I drop and recreate them periodically to ensure that the dependancies are all correct and SQL uses the best plan. Is there a way to have SQL Server do this for me???
I am designing a Database Application that covers Inventory System. And I am now in a dilemma of chosing which design to track Inventory stock better, in performance, reliability, and error free?
1st Design
PRODUCT TABLE ItemID ItemName Price QtyOnHand ..and other unique info of the product..
and similar design with PURCHASE+DETAIL, SALESRETURN+DETAIL, PURCHASERETURN+DETAIL, ADJUSTMENT+DETAIL
The later design does not hold QtyOnHand, but only save QtyBegin instead. To get the QtyOnHand, it uses views/stored procedure with Union Query, so it looks like this:
Hi All, I'm trying to make a trigger on a table. If the data changed, the trigger will fire and copy the table into another table. Can u give me examples on Triggers for Update.. Thanks
I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between
CREATE TABLE #test1( [NAME] [nvarchar](128) NOT NULL, [DEFINITION] [nvarchar](max) NULL, [DEFINITION_bk] [nvarchar](max) NULL, [type] [char](2) NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I would like to use the view of the company data in the following stored procedures
without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.
Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.
INSERT INTO State(StateName) VALUES(@StateName)
Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.
FROM Company AS C INNER JOIN Address AS A ON C.AddressID = A.AddressID INNER JOIN State AS S ON A.StateID = S.StateID INNER JOIN Phone AS P ON C.PhoneID = P.PhoneID
Here is code for the insert trigger on the view
CREATE TRIGGER InsertTriggerOnCustomerView ON ViewOfCompany INSTEAD OF INSERT AS BEGIN
DECLARE @CompanyName VARCHAR(128) DECLARE @AddressID INT DECLARE @Street VARCHAR(256) DECLARE @City VARCHAR(128) DECLARE @StateID INT DECLARE @StateName VARCHAR(128) DECLARE @ZipCode INT DECLARE @Country VARCHAR(128) DECLARE @PhoneID INT DECLARE @PhoneNumber VARCHAR(32) --DECLARE @CompanyID INT
INSERT INTO Address(Street, City, StateID, Country, ZipCode) VALUES(@Street, @City, @StateID, @ZipCode, @Country)
SET @AddressID = SCOPE_IDENTITY()
INSERT INTO Phone(PhoneNumber) VALUES(@PhoneNumber)
SET @PhoneID = SCOPE_IDENTITY()
INSERT INTO Company(CompanyName, AddressID, PhoneID) VALUES(@CompanyName, @AddressID, @PhoneID)
END
The stored procedures are here.
CREATE PROCEDURE GetCompany ( @CompanyID INT ) AS BEGIN SELECT CompanyName,Street, City, StateName, ZipCode, Country, PhoneNumber FROM ViewOfCompany WHERE CompanyID = @CompanyID END GO
CREATE PROCEDURE AddCompany ( @CompanyName VARCHAR(128), @Street VARCHAR(256), @City VARCHAR(128), @StateName VARCHAR(128), @ZipCode VARCHAR(128), @Country VARCHAR(128), @PhoneNumber VARCHAR(32) ) AS BEGIN INSERT INTO ViewOfCompany VALUES(@CompanyName, @Street, @City, @StateName, @ZipCode, @Country, @PhoneNumber) END GO
Hello Guys!i have been working with oracle with quite a time. No i migrated to sqlserver 2000 and i want to create a trigger on a table.the trigger function has to update the Modification field to getdate()whenever a row is being updated.i tried lots of thingsif anyone can help i would appreciate a lot!Regards
All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:
Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated.
Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?
Hi everybody,I just wrote my first two triggers and from the minimal amount of testing Ihave done, they work! However, I was hoping I could get some feedback fromthose of you more experienced in writing triggers.Here is the first one:CREATE TRIGGER DecreInters ON InteractionFOR DELETEASdeclare @stu INTdeclare @num INTselect @stu = Student_FK from deletedselect @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num - 1FROM StudentWHERE Student.Student_Key = @stuHere is the second one:CREATE TRIGGER IncreIntersON InteractionAFTER INSERTASdeclare @stu INTdeclare @num INTdeclare @last_rec INTselect @last_rec = @@IDENTITYselect @stu = (select Student_FK from Interaction where Interaction_ID =@last_rec)select @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num + 1FROM StudentWHERE Student.Student_Key = @stuAre there any shortcuts I could use or things I could do to make thesetriggers more efficient. Please give me some feedback and let me know ofany problems that might possibly be caused due to my doing this improperly.Thanks ahead,Corey
We have trigger on a table. The trigger implementation is to insert a record in another table. If any error occurred in the trigger then the trigger should log the error to a file on the file system.
CREATE TRIGGER [myTRIGGER] ON [dbo].[MyTest] AFTER INSERT AS declare @errorcode int; INSERT INTO MySecondTable (id, myvalue) values (null, 'hey') set @errorcode=@@ERROR if (@errorcode <>0) print 'Error occurred with error code "' + CONVERT(varchar, @@ERROR) + '"'
Problem If the insert statement fails, for example because of a null violation, then trigger aborts and never reaches the next step in the trigger T-SQL code. IS this a limitation or there's somthing wrong on the above code? Thanks
I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}
I am trying to change values of two tables in my sql server 2000 database. When one of the tables is modified in some way like adding/updating a record, I need a trigger procedure to copy this new data from the first table to the second. Problem is, how do I get the newly inserted or updated data from the first table? How do I specify that I only want the data which caused the trigger to execute? Anyone know?
I am using the import/export wizard to import all the objects from one database into another.My problem is that any views that use User Defined Functions are failing because the view is being created before the UDF.I have searched for this error, and found the following microsoft article:http://support.microsoft.com/kb/300272/en-usThe article says that I should upgrade to the latest service pack. The problem with this is that I already have the latest service pack When I run the following:SELECT @@VersionI get:Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Could anyone please help me solve this problem.Jag
I wonder if anyone can help me - I am trying to write a little application which recompiles any views dependent on a given view. I have a script which will identify any views containing a given text string, but I now need a way to force SQL Server to refresh its cache info (or recompile) the views. I have to admit that I am unclear on exactly how the caching works; i have tried using sp_recompile, but this does not seem to do the job. Does anyone have any ideas?
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong. Thank you
Lalitha writes "Can I use DML statements against indexed views? If yes how it works internally, means will the pages gets locked during update and inserts and when the base tables get reflected of these modified data?"
Recently we upgraded our server from SQL 7.0 to SQL 2000. I created anew view in Enterprise Manager which pulls data from a linked server'sdatabase. When I write the view...CREATE VIEW dbo.VIEW1ASSELECT *FROM LinkedServerName.DatabaseName.dbo.TableNameand I click on the run button it changes to:CREATE VIEW dbo.VIEW1ASSELECT *From LinkedServerName.DatabaseName.dbo.TableName TableName_1I can still see the correct results, but the users can't- Once Iremove the TableName_1 they can see the results.When I tried creating a view and did not use a linked server,everything worked fine. Anyone have any answers?
I'm aware that when bulk loading to a SQL 2005 database through an OLE DB Destination, you can enable the FIRE TRIGGERS option. Is this option available when your database target is SQL 2000? I can't find it in the Properties or other windows.
Hello, Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well? Thanks.
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hi Guys,I have a website that has a log in box, when a user logs in it automatically inserts a 1 in my users table and a little icon changes color on the web page to indicate that a person is signed in, my problem is that sometimes my users don't log off using the signout button which then puts a zero back in my table and a different coloured icon is shown. I cant seem to force them to use this button so I being new to sql server wondered if I could use a trigger to set the field to 0 after say half an hour.I also have a table that has various projects in it and they each have a field that has an estimated end date, I wondered if there was some sort of trigger I could use to fire off an email when the todays date passes that end date to notify evryone it was overdue. If this can be done please could you provide me with an example, Many thanks
I need help writing a delete trigger. The table that I want to audit is [dbo].[MCMESSAGE]. When there is a delete on this table I want to write the deleted row to an audit table called [dbo].[AUDIT_MCMESSAGE]. The name of my trigger is [DEL_MCMESSAGE_TR].
This is what I have so far :
CREATE TRIGGER [DEL_MCMESSAGE_TR] ON [dbo].[MCMESSAGE] FOR DELETE AS INSERT [dbo].[AUDIT_MCMESSAGE]
When I test this I delete a record from [dbo].[MCMESSAGE] I get this Error : "[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated."
As per microsoft's documentation we can create more than 3 trigger per table in sql 7.o. I just like to know the meaning for this statement. If it's the case we can create more than one update trigger per table. Then which trigger will fire first and then how the other update triggers will perform. Can you please provide detailed explanation.
Hi, I know how to write a trigger on a table in which any insert into table A will fire an insert trigger... What I am tring to find out if the following is possible: I want to have a trigger on a table A where it fires ONLY when anumber of rows are inserted NOT row by row.... for example. I am importing acouple of hundreds of rows into table A. Once the import process is done, I want to fire a trigger to count the total number of rows inserted into Table A... and insert the table Name, #of rows into Table called Tracking table...
Can anyone help me in finding solution for this... thanks
