SQL Server 2008 :: INSTEAD OF Trigger On A View
Mar 11, 2015
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
ADVERTISEMENT
Jan 22, 2015
We have setup a report server where a db's tables are refreshed from a backup daily.
All SP and views are created in a different DB but look at the daily refresh tables
Can I setup the DB that creates the view to point at the DB with the tables so when I say new view it looks at the tables in that DB ...
View 4 Replies
View Related
Jun 21, 2010
Can I view the log cache size in SQL Server memory any DMV's which states that.
View 9 Replies
View Related
Mar 2, 2015
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.
View 4 Replies
View Related
Jul 23, 2015
Can I accomplish encrypting a column with a trigger similar to below, but without the 'SSN' column?
I added the SSN column just so I could pass an UNencrypted value. Seems round-about & clunky.
This works:
Insert into TestSSNs(UserID, SSN, Name) VALUES (NEWID(), '890-12-0007', 'Gina')
---------------------------------------------
CREATE TABLE TestSSNs (
UserIDuniqueidentifier,
SSNVarchar(11) , --plain text, should always be null
eSSN VarBinary(128), --encrypted value
[Name]Varchar(50)
);
CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs
[code]....
View 3 Replies
View Related
Oct 25, 2015
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??
View 3 Replies
View Related
Jul 20, 2005
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 Related
Sep 4, 2015
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 :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
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.
View 4 Replies
View Related
Jul 23, 2012
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)?
View 2 Replies
View Related
May 27, 2015
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]
View 6 Replies
View Related
Jun 2, 2015
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.
View 9 Replies
View Related
Aug 26, 2015
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,
[code]....
View 3 Replies
View Related
Sep 15, 2015
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?
View 9 Replies
View Related
Oct 29, 2015
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?
View 9 Replies
View Related
Jun 5, 2012
I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :
Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1
Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.
Here is the code for the stored procedure :
CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END
View 5 Replies
View Related
Mar 11, 2015
I have created an insert trigger to update an address column with a default when it is null, for different clients using a case statement.
When I import data using the import wizard, the trigger doesn't fire.
Doesn't SQL see an import as an insert?
How can I get this trigger to fire for an import?
View 0 Replies
View Related
Apr 6, 2015
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).
This is on SQL Server 2008R2.
View 2 Replies
View Related
Mar 25, 2015
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.
View 0 Replies
View Related
Mar 14, 2015
I have two tables. Order table and order detail table.
What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.
I can't get working with on insert trigger . How to go around with trigger ?
View 1 Replies
View Related
Apr 10, 2015
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.
View 9 Replies
View Related
Oct 11, 2006
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 Related
Jul 9, 2015
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
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
View 9 Replies
View Related
Mar 6, 2013
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?
View 9 Replies
View Related
Sep 9, 2015
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).
View 9 Replies
View Related
Apr 21, 2015
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.
View 2 Replies
View Related
Sep 23, 2015
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.
View 9 Replies
View Related
Apr 22, 2005
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
View 2 Replies
View Related
Nov 2, 1998
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
View 2 Replies
View Related
Sep 24, 2005
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
View 7 Replies
View Related
May 21, 2008
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
View 2 Replies
View Related
Mar 24, 2006
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
View 3 Replies
View Related
May 22, 2006
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.
View 9 Replies
View Related
Jun 10, 2015
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 Related
