Hi all!
How do u view the triggers in sql 2000 qry analyzer. I know you can use sp_helptrigger-- which will give the list of triggers-- -- But, I need to be able to run command from qry analyze and see the script used to create triggers
Having limited access to the design tables-- wont be able to see trigger from sql design table
Thank you
Josephine
I have several views in a database that I need to edit in SSMS 2008.
If I right click on a view, I can select "Design" and do the changes and save. Everything saves ok.
However, I cannot enter comments that way. I have tried View Script As -> ALTER To -> New Query Editor Window but this method creates a new view e.g. SQLQuery1 i.e. I can't save the origial view name.
Is there a way to alter views in code and save them back into the database under their original view name?
I inherited a WinCE.net application that uses SQL Server CE as the database. We use Visual Studio 2003 to write the program. Using the query analyzer on the device is difficult and I would much prefer to do my database views/research on my desktop. I could even copy the SDF over to the desktop, slice and dice it and then return it to the compact unit. But what do I need to have on my desktop so I can view these SDF files? Thanks.
View 10 Replies View RelatedCurrently, I have a set of data that is available on a SQL Server. I want to be able to display the information in the front-end (ie. Access form, excel, webpage?), and if the user wants to edit any information they may do so and with a click of a button, submits to the sql server.
View 1 Replies View RelatedI created a few in SqlServer2005 DEV version and ran them manually with success. But where are they? I chose the SqlServer storage option and I see them when I create Job and steps but how do I view/edit these packages from SqlServer Mgmt. Studio? The SSIS service is running.
View 6 Replies View RelatedHello,I have inserted a drop down list in my Edit template of Grid view. This DDL should control the User_ID who is responsible for the Computer being edited. However I want it to display the User Names rather than the user IDs. I have a COMPUTERS and a USERS table. They are related by having User_ID in both. Below is my grid view with the parts i feel relative highlighted in bold:<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Computer_ID" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:CommandField ShowEditButton="True"/> <asp:BoundField DataField="Computer_ID" HeaderText="Computer_ID" InsertVisible="False" SortExpression="Computer_ID" ReadOnly="true" /> <asp:BoundField DataField="Computer_Name" HeaderText="Computer Name" SortExpression="Computer_Name"/> <asp:BoundField DataField="Manufacturer" HeaderText="Manufacturer" SortExpression="Manufacturer"/> <asp:TemplateField HeaderText="User Name" SortExpression="Name"> <EditItemTemplate> <asp:DropDownList ID="DDL_Name" runat="server" DataSourceID="SQLDataSource2" DataValueField="User_ID" SelectedValue='<%# Bind("User_ID") %>'></asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblName1" runat="server" Text='<%# Bind("Name") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:AjaxControlToolkitWebSite1App_DataAssetDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="<%$ ConnectionStrings:AssetDatabaseConnectionString.ProviderName%>" SelectCommand="SELECT * FROM USERS" > </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:AjaxControlToolkitWebSite1App_DataAssetDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="<%$ ConnectionStrings:AssetDatabaseConnectionString.ProviderName%>" SelectCommand="SELECT COMPUTERS.Computer_ID, USERS.User_ID, COMPUTERS.Computer_Name, COMPUTERS.Manufacturer, USERS.Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID" UpdateCommandType="StoredProcedure" UpdateCommand="StoredProcedure1" > <UpdateParameters> <asp:Parameter Name="Computer_ID" Type="Int32" /> <asp:Parameter Name="User_ID" Type="Int32"/> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Computer_Name" Type="String" /> <asp:Parameter Name="Manufacturer" Type="String" /> </UpdateParameters> </asp:SqlDataSource> <br /> <br /> </form></body></html> THANKS =]
View 6 Replies View RelatedI represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.
My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.
Our database structure is a trade secret and we cant reveal the structure to the client.
please answer this question by email to me at farhandotcom@gmail.com
Thanks & Regards
Farhan
hi...
is it possible to create a trigger on view object....
usually we divide our table in to a smaller pieces and use view to get the big picture of it..
my task is to record any/every select statement on each table.. so logically we create view and is it possible to use trigger(to record the accesses) in view..
please help..
or just give me a link to get that info..
tnx
Can anyone recommend a good reference book on views, stored procedures (system stored
procedures as well) and triggers? I am in need of assistance in these areas.
Thanks!
Toni
I got a view that gest information från a certain table. Then i got a trigger that should copy new information from this view to a specific table. How should this trigger know when new information has been inserted in the View?
CREATE TRIGGER copyRow
ON person_View
INSTEAD OF INSERT
AS
BEGIN
INSERT into kontakt (nr, telefon, adress)
values ('2', '3', '4')
commit transaction
END
Hi,
Is there a way to create a Trigger on the Systemview [sys].[dm_exec_connections] ?
The Statement below Fails.
CREATE TRIGGER [dbo].[TG_CLEAN_LOCK_ID] ON [sys].[dm_exec_connections]
AFTER DELETE
AS
I need a trigger to be fired when a Connection ([sys].[dm_exec_connections] .[connection_id]) drops.
Is it principally impossible to create a trigger on a system view or is it just a lack on permissions making my SQL fail?
Is there any other way for surveying wether a connection is alive or not, without polling?
Thanks in advance
Raimund
Hi,
just started to write my first trigger for a view. Of course I got some errors, which I could could resolve except one.
Whenever I run my script I do get the following message:
Msg 213, Level 16, State 1, Procedure IO_Trig_INS_Zuordnung_Alles, Line 11
Insert Error: Column name or number of supplied values does not match table definition.
The Code where the error occurs according that message is the following:
CREATE TRIGGER IO_Trig_INS_Zuordnung_Alles ON Zuordnung_Alles
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Zuordnung. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT Z.[Anlagen-Nr_Z]
FROM Zuordnung Z, inserted I
WHERE Z.[Anlagen-Nr_Z] = I.[Anlagen-Nr_Z]))
INSERT INTO Zuordnung
SELECT [Anlagen-Nr_Z], [I-Nr], [an_A-Nr], [APS-Reg], [KSt des Inventars], [Gelände_Raum], [Servicenummer], [S-Nummer], [Hostname], [LOGIN-Name], [Mitarbeiter von], [Kategorie], [Verwendung], [Hersteller und Typ], [Ausstattung], [F-Nr], [Board], [Bios], [Prozessor], [Cache], [RAM], [SCSI-Contr], [CD-Rom], [Festplatten], [Wechselplatten], [Sonderausstattung], [Graphik], [Sound], [MPI], [NW-Karte], [MAC-Adr], [DHCP], [IP-Adr], [Netz], [Port], [Segment_ID], [NAP], [NW-Karte_2], [MAC-Adr_2], [DHCP_2], [IP-Adr_2], [Netz_2], [Port_2], [Segment_ID_2], [NAP_2], [Bemerkungen], [Betriebssysteme], [Dual-Boot], [geplante Maßnahmen], [Servicearbeiten], [aktualisiert], [wieder frei], [zurück von], [COB-Kostentyp], [COB-Import], [Dummy3], [Dummy4], [Inventursuche
FROM inserted
ELSE...
I did check on the columns serveral times, also I wrote them back with vba and used that but nothing helps. I would appreciate any help on possible errors in that code.
i have a question about updating views:for example: lets assume we have a table which keep the users score and we have a view which created from so many tables and calculate the sum of the users scores. now if one of the user's score changes all the view created again or just part of the view will update?i ask this to find out how can i design my database? how can calculate users scores?i can wrote a trigger to do it or use the view. which way is faster and better in this case?
View 9 Replies View RelatedI want to create a trigger on a view for insert, but I got a message said 'Invalid object name'. I can select from this view. Followings are my scripts and error message:
(run on SQL 2000)
create table t1 (c1 char (10) NULL )
create view vt as select * from t1
create table log1 (c1 datetime NULL )
create trigger tr1 ON t1
for insert
AS
insert into log1 values (getdate())
----------all above succeed
create trigger tr2 ON vt
for insert
AS
insert into log1 values (getdate())
Server: Msg 208, Level 16, State 4, Procedure tr1, Line 1
Invalid object name 'vt'.
Thanks!
Theresa
Hello,
I have a problem that definitely has me stumped.
I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.
I keep getting the message...
Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.
Where vwDC_DocInfo is the name of the view.
Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.
The script is included below...
CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS
DECLARE @ModifiedDate AS DATETIME
SELECT @ModifiedDate = GETDATE()
-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM DELETED DEL
-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM INSERTED INS
I have a view made up of a few base tables and another view. I have created an INSTEAD OF trigger on this view, but it doesn't seem to fire whenever a new record shows in the view. The purpose of the trigger is to insert a sister record in a table whenever a new record shown in the view. Here's the catch, the table that the trigger is supposed to insert into is not a base table within the view and the view is not an updatable view. My question is... Do INSTEAD OF triggers only affect the base table(s) within the view and does the view itself have to be an updatable view?
View 3 Replies View RelatedHello all...I'd appreciate any help on this one.
I created a View...the view looks at four seperate tables.
Next, I created an Instead of Update trigger on that view. It works fine...for a regular UPDATE...SET.
However, it throws an error when I try an update and self join based on that view:
Update T1
Set RateUsed = T1.RateUsed
From Taxroll..Taxroll T1 Join
Taxroll..Taxroll T2 on
T1.Asmt = T2.Asmt and
T1.Taxyear = T2.Taxyear
Where T1.Asmt = '123456789012'
And T1.Taxyear = 2007
And T2.RollChgNum = ''
And T1.RollChgNum Like '%X'
And IsNull(T1.RateUsed,'') > ''
Msg 414, Level 16, State 1, Line 2
UPDATE is not allowed because the statement updates view "Taxroll..Taxroll" which participates in a join and has an INSTEAD OF UPDATE trigger.
There are a few caveats:
First, I thought the join issue was in the view itself. I re-created the view using no joins...all subqueries and still get the error. Second, I re-created the Instead of Update Trigger with no joins, and still get the error.
Thanks!
In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
Code:
SELECT TOP (100) PERCENT O.EID, O.OStart, O.OEnd, O.OID, T.Title, P.PStatus AS PS, dbo.CalcAge(O.OStart, ISNULL(O.OEnd, CURRENT_TIMESTAMP)) AS ODuration, O.PID,
O.Residence, O.b55, O.SplitItem, O.PeakStaff, O.ResidenceSub, O.Negotiator, O.Supervisor, O.TimeType, O.BreakPM, O.WorkEnd, O.Lunch, O.BreakAM, O.WorkBegin,
[code]....
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies View RelatedHello all,SQL Server 2000 documentationhttp://www.microsoft.com/technet/pr...rt10/c3761.mspxstates that if view is using "NOT NULL" columns of a base table, theninsert/update performed on a view must provide dummy values for thosecolumns, and code of the trigger should ignore them.But I cannot reproduce this restriction. Code below pasted to QueryAnalysershows that I can not supply dummy values for "NOT NULL" fields when I updateview and still have update done. What do I miss ?VT/*--setup step 1. execute only inside of this commentSET NOCOUNT ONCREATE TABLE TestTable(keyField INT IDENTITY(1,1),dataField1 INT NOT NULL,dataField2 INT DEFAULT 1 NOT NULL)*//* --setup step 2. execute only inside of this commentCREATE VIEW TestView ASSELECT * FROM TestTable*//*--setup step 3. execute only inside of this commentCREATE TRIGGER TestViewTrig_IUON dbo.TestViewINSTEAD OF UPDATEASBEGINSET NOCOUNT ONUPDATE TestTableSETDataField1 = inserted.DataField1,DataField2 = inserted.DataField2FROMTestTable ttINNER JOINinsertedON inserted.KeyField = tt.KeyFieldEND*//*--setup step 4. execute only inside of this commentINSERT INTO TestTable (DataField1,DataField2) Values (1,2)INSERT INTO TestTable (DataField1,DataField2) Values (3,4)INSERT INTO TestTable (DataField1,DataField2) Values (5,6)*/SELECT * FROM TestView-- SQL Server lets me not specify DataField2 when update DataField1 orreverse,-- which is opposed to what documentation saysUPDATE TestView SET DataField1 = DataField1 + 1 where KeyField = 2UPDATE TestView SET DataField2 = DataField2 + 1 where KeyField = 3SELECT * FROM TestView/*-- remove test environmentDROP VIEW TestViewDROP TABLE TestTable*/
View 3 Replies View RelatedI created a view that joins several tables together and added an instead of insert, update trigger on it. My trigger works when I do an update/insert directly to the view, but it doesn't seem to fire when an insert/update is made to any of the tables that the view is based on. Is there a way to have the trigger fire when something happens to the tables that the view is based on? The application that the database is using is something that was purchased so I don't have access to the code to change any SQL statements that it's using.
View 5 Replies View RelatedSuch as check all triggers that assign value to some columns ?
Thank you very much.
I am maintaining an application where most of the business rules are inTriggers, Stored Procedures and User Defined Functions. When a bugarises, it can get very tedious to debug. Today for example, I wantedto modify a function that was being called by a trigger. The problemis that I don't want to change the function, for fear that it is beingcalled by one of the other SP's or triggers in the database (there arehundreds of them)Essentially, I need a tool that allows me to view where functions andsp's are being referenced from. At the very least, I'd like to performa "full text search" in the database objects, so that let's say I havea function named "fn_doSomething", I can search the schema for thisstring and get all the places where it appears.As you can see, I'm in the dark here. I've never worked on a systemwhere all business rules are at the database level. If you know of atool that does what I describe above, or anything else that wouldfacilitate my life, please let me know!Thanks for your help,Marc
View 5 Replies View RelatedHi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
Hello,
I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason?
Thanks,
Ravi
Hi,
I face this error when i try to run my store procedure.
The sample of store procedure as following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END
OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)
SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END
CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF
anyone can help me identify this error?
Thanks
Regards,
Jojomay
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?
View 1 Replies View RelatedI have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
View 4 Replies View RelatedEdski writes "Help please,
below is my sql query, when I run it, I get the right result with one problem. I get same customers with same email many times. I would only like the same customer shown one time. Checking it by email.
SELECT od.pName, od.dIndx AS dindx, od.pIndx AS pindx, od.device AS device, c.Cust_Id AS cust_id,
c.First_Name AS First_name,
c.Last_Name AS last_name, c.Email AS Email, c.Phone AS phone,c.company, h.productCode AS
product_code, DATEDIFF(dd, GETDATE(), h.expirationDate)
AS Difference, h.hotSyncId AS regcode, od.autorenewal AS autorenewal, od.installment AS
installment
FROM Orders_Detail od INNER JOIN
Hot_Sync_ID h ON od.dIndx = h.dIndx INNER JOIN
Customers c ON c.Cust_Id = h.cust_id
WHERE (od.prenewal ='False') and (od.autorenewal = 'no') and (c.newsletter <> 0) AND
(DATEDIFF(dd, GETDATE(), h.expirationDate) = '27') AND (c.company <> 'University of Alberta')
ORDER BY difference, c.Last_Name, h.productCode"
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!