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?
A job runs every morning at 3.00 am to back up a database. Many of the tables have triggers on to write updates, deletes and inserts to audit tables.A typical trigger looks like this.
USE [ProjectDB_Live] GO /****** Object: Trigger [dbo].[trgStakeHolders] Script Date: 03/02/2015 10:23:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[code]....
I added a trigger to a table over the weekend - structured the same as the one above and, when the back up ran last night, it copied every row in the table into the audit table as if every row in the table had been updated.
the disable trigger will be enable again.Because I already disable the trigger on last month, but when I check through the database, it enable again.I understand that, when we restore the database, all the trigger will be enable.How about SQL Cluster? will it enable the trigger??
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
A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC] GO /****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vwSurgicalVolumes] AS SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT * FROM OPENQUERY ([PORTALWEBDB], 'SELECT --AllLists AL.tp_ID AS ALtpID ,AL.tp_WebID as altpwebid ,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
Here's the scenario. We tried to create a full text indexing on a view, unfortunately the view was created based on Left outer join. Hence we were pushed to do the following.
We planned to create a table based on the data that is rendered in this view(this view is formed by combining 14 tables.) . Finally we were able to create a full text indexing on this new table. For simplicity let us called this new table as tableA.
Now the problem is how can we update (if any data is changed on one of the concrete tables of the view) or insert (when new data is inserted in one of the underlying tables of the view) the respective data into this new table - tableA which was created based on this view.
In other words, there should be a sync between the view and tableA. Is it possible to achieve this ???
View is formed based on different tables (not based on tableA)?
We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50)) INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6') SELECT * FROM #SourceDatabase DROP TABLE #SourceDatabase CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50)) INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6') SELECT * FROM #ArchiveDatabase DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1] CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2] CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3] CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4] CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5] CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
I gave a user all required permission to view the SSRS report. User is able to select from the dropdown list but unable to view the data, It is showing a blank screen.
I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs] WITH SCHEMABINDING AS SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime, purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne, purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.
Would SSRS be an option? where I can create a report of the view and schedule it?
Does any know the process I need to follow?
Do I have to uses SSIS ? and then set it up as SQl server job?
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07') SELECT M.[Description] ,A.* ,M.Threshold ,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.
What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.
Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).
I'm creating Indexed view by JOINING multiple tables and trying to create FULL TEXT search index. Unique column is generated by concatenating to ID columns from different table. I can successfully able to create unique index however when trying to create FULLTEXT INDEX getting below error.
"A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
The message clearly says the column should be single-column index, non-deterministic.
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABC]( [id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABCD]( [id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
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
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable] ON [dbo].[t_account]-- A DB level trigger FOR UPDATE --Event we want to capture AS IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure: USE [DB1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated the data in row 1 was not committed Error source .Net SqlClient Data provider. Error Message: the operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
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.
All the logins with sysadmin role are locked out in one of the SQL server. Also in that instance Logon trigger is implemented which allows only certain users with Windows Authentication to logon.
This server is converted into single user mode by using command prompt. Now we are attempting to connect to SQLServer using command sqlcmd -S FTCPU3239 (since we do not have any other login) and create a new login with sysadmin role but are refused due to logon trigger. it says Login failed for login 'DomainNameUserName' due to trigger execution.
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..
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.
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
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
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]
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?
