Instead Of Triggers On A View
Sep 23, 2004
Everyone,
I'm attempting to get Instead of Triggers working with little success. I created a small sample table to attempt to get it to work:
1. Here is my base table:
CREATE TABLE [dbo].[Snowball] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
2. Here is my View on the base table.
SELECT dbo.Snowball.*
FROM dbo.Snowball
WHERE (Type = 'BIG')
One this view I have the following trigger:
CREATE TRIGGER TR_Snowball_2 ON SnowballView INSTEAD OF INSERT as
BEGIN
INSERT SNOWBALL (id, TYPE )
Select inserted.type,inserted.id from inserted
END
I have the type and id reversed to see if the trigger fires. I can't seem to get this trigger to fire? Any thoughts?
View 1 Replies
ADVERTISEMENT
Dec 20, 2001
How to view code for existing triggers on a table?
thanks.
View 1 Replies
View Related
Feb 28, 2007
I need a way to view all existing triggers in our MS Server databaseespecially since I don't know what tables are involved.Thanks,kwilla
View 2 Replies
View Related
Jul 20, 2005
Hello,I am pretty much a newbie with SQL server. I have what is probably apretty stupid question. In SQL Enterprise manager, is there a way toeasily see all triggers, or, even better, all tables and thereassociated triggers? I suppose at the least in a way similar toviewing all saved functions or views.Thanks,Ryan
View 4 Replies
View Related
Jul 23, 2005
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
View 4 Replies
View Related
May 12, 2008
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.
www.handleysonline.com
View 12 Replies
View Related
Jul 23, 2005
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 Related
Jul 24, 2012
Write 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]...
View 2 Replies
View Related
Aug 24, 2007
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!
View 3 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Feb 17, 2006
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
View 4 Replies
View Related
Oct 27, 2006
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
View 1 Replies
View Related
Jan 3, 2007
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
View 1 Replies
View Related
May 31, 2007
Hello All,
I have to write Trigger for Update,
I have two tables, one is for storing records of current values, and one is for storing history of values.
How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values.
Help me.
General Problem
View 1 Replies
View Related
Jun 15, 2007
I need to create a set of rows every time a new row is inserted into a table.
Example (I think this would work)...
select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id)
Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values?
Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger?
Sorry if I sound confused. I am.
View 2 Replies
View Related
Dec 6, 2007
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value.
SET @ActionTaken = 'A'
IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
View 3 Replies
View Related
Dec 21, 2007
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
View 3 Replies
View Related
May 9, 2004
Hi All,
I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
Thank you,
Mike
View 3 Replies
View Related
Jun 17, 2004
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set
(Lastname = @Lastname)
where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS
IF UPDATE(lastname)
BEGIN
RAISERROR ('cannot change lastname', 16, 1)
ROLLBACK TRAN
RETURN
END
GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
View 10 Replies
View Related
Mar 5, 2006
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
View 3 Replies
View Related
Dec 4, 2001
l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR insert,Update AS
IF left('Loan_No',3)='ABC'
update Test_TRG
set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF'
update Test_TRG set
loanbook = 'NEWDEF'
where loanbook is null
else
update Test_TRG
set loanbook =left('Loan_No',3)
where loanbook is null
View 4 Replies
View Related
Dec 5, 2001
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ".
The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if
The left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=MCG and its null then NEWMCG
left(loan_no,3)=KVS and its null then NEWKVS
left(loan_no,3)=MFS and its null then MFS
left(loan_no,3)=TCR and its null then TCR
left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained
in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it
l get an error.
CREATE TRIGGER UpdTest_TRGData
ON Test_TRG
FOR INSERT, UPDATE
AS
UPDATE Test_TRG
SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG'
WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS'
WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS'
WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 )
END
FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK
-- Fill in Primary Key or other Join Column(s)
WHERE LEFT( i.Loan_No, 3 ) = 'MCG'
OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL )
OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11
Invalid column name 'PK'.
View 1 Replies
View Related
Jan 11, 2002
I an loading records from a flat file into a table, which is done everyday by a scheduled job in SQL Server 7.0.
How can I make sure that if the job is run twice in a day for some reason that the same rows are not inserted into the table again? Do I have to write a insert trigger on the table ??? If so how can I achive the objective ??
View 1 Replies
View Related
Jan 31, 2002
Hi! i have a simple question...is possible to create a trigger who affect multiple tables?
The idea will be something like this:
create trigger mytrigger
on sales, users
as...
of course, this don´t work :)
View 1 Replies
View Related
Aug 3, 2001
Hey everyone...
I'm working on trying to figure out how to update the child table from the parent via a trigger. This works fine as long as the value is in the child table. If it's not, then I get my foreign key violation. So, My next thought was to simply put in a begin tran / rollback tran within the trigger... My question is this : If I have multiple triggers on the parent table all based on the update of the key field (and the child tables... some 7 of them) all have FK's, if I rollback one trigger does it rollback the entire transaction? Or just the functionality of that trigger? From what I've read, it appears as if it's the entire transaction... if so, how do I get a trigger to 'ignore' itself or not fire based on a select criteria that i have within that same trigger...
Thank you...
View 3 Replies
View Related
Oct 24, 2001
I just tried creating my first trigger and I'm a little bit confused. I am trying to see if certain fields have changed and will do that by checking the values of the fields in the "master" table and the "Inserted" table. I wasn't sure which table would have the old data and which would have the new, so I created the following trigger to help me determine which was which:
CREATE Trigger upd_cost_master On tbl_cost_master
For Insert, Update As
Declare @U_Name varchar(65)
Declare @O_Name varchar(65)
Select @U_Name=I.submitted_by, @O_Name=O.submitted_by
From tbl_cost_master O Inner Join Inserted I On O.autokey = I.autokey
Print 'Inserted: ' + @U_Name
Print 'tbl_cost_master: ' + @O_Name
Commit Tran
To my surprise, @U_Name and @O_Name contained the same values. I though that one table contained the old values and one contained the new. Was I wrong? If so, is there anyway to compare the Old and New values through a trigger?
Chris
View 1 Replies
View Related
Jul 20, 2000
Hi,
I'm using SQL Server 7.0. I have an insert trigger on table t05r. I populate table t05r with a DTS package. The trigger is supposed to add the record which was inserted into t05r, into t39r. After I run the DTS package to populate t05r, I checked t39r and no records where added. What is going wrong? Is it that DTS uses bulk copy and triggers don't fire on bulk copy?
Any help would be greatly appreciated.
Thanks in advance,
Darrin
View 1 Replies
View Related
Nov 1, 1999
I have an insert trigger on table A which inserts records into table B.
This insertion into B fails( or may fail ) at certain conditions.
I do not want the insertion into A to be affected because of this. Any ways to achieve this ?
View 1 Replies
View Related
Oct 8, 1999
Hi,
I have 3 tables A,B C and Triggers on each table like.
for insert/update on table 'C' calculates sum(xxfield) and updates
table 'B'.
for insert/update on table 'B' calculates sum(yyfield) and updates
table 'A'.
This works fine if I insert rows OneByOne.
Problem :- Doesn't work if I post entries(hundreds) in to table 'C' using a Query.
whats happening. Can anyone help Me????????
View 1 Replies
View Related
Nov 12, 2001
How do I know if the triggers on table has been disabled or not. Thanks for your ideas!!!!
View 3 Replies
View Related
Nov 21, 2002
Hello,
I've update triggers on my table, But for some of the transactions, I don't want the triggers to fire. I know in Sybase there are set triggers off. In MS SQL, Is there any similar commands which will set triggers off for particular sql statements.
Thanks A Lot
Sejal
View 2 Replies
View Related
May 1, 2003
Hi! I am having trouble with setting triggers while doing my project. The project is on ATM machine driven through SQL. So I have Date of Transaction in one table and Account Expiry in another.
I want to set the trigger to check Account expiry when doing transaction so i wrote T-SQL
select........
getDate()-Dateoftransaction=expiry
begin '...'
end
the system saying I have got an error near the word 'begin'.
I am desperately needing help. running out of ideas.
Thanks in advance.
View 1 Replies
View Related