December 6, 2010 at 8:45 pm
Hi everyone
I have a requirement to issue an "ALTER VIEW" statement preferably from a tables update/insert trigger.
I have come across two issues with this though.
1. If you try to issue an "ALTER VIEW" from a trigger, I get a syntax error;
"Incorrect syntax near keyword 'VIEW'"
2. As a workaround I decided to issue the "ALTER VIEW" from the trigger via sp_executesql. Whilst this gets around the syntax error, when I update a record which fires the trigger, it now hangs.
It works fines when issued from a stored procedure, just not from a Trigger.
Any ideas?
Cheers
Doug
December 6, 2010 at 11:02 pm
doug.milostic (12/6/2010)
Hi everyoneI have a requirement to issue an "ALTER VIEW" statement preferably from a tables update/insert trigger.
:blink: Why would you want to do that? I've got to hear this business rule. That's wild.
It works fines when issued from a stored procedure, just not from a Trigger.
Any chance you're using the view in the statement that triggers the alter view trigger?
You're performing DDL at the same time as DML, in the same transaction. At *best*... expect some delays. My guess is you're deadlocking or getting hung up in some lock manipulation against the schema of the view.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 6, 2010 at 11:27 pm
Long story but it is feasibly that I am encountering a deadlock on the schema ... hmmm not sure but I'm no SQL expert.
I'll try to explain.
I have a stored procedure that returns pivoted data.
I then have a View based on this stored procedure which is utilitise elsewhere in the database.
The problem i have is that when you create a View the field names returned by the SP are automatically hard coded in the view. You cant permanently define the view with "Select * From .... " in order to automatically pick up any new columns return by the views data source. You can however type in the sql "Select * From...." when you initially definethe view, but then it immediately returns all the field names from the data source and you have no choice but to save the view with all the individual field names returned from its data source, in this case a SP.
Now, as records are added to the tables, it is inevitable (in my database) the the SP will return an additional column/field at some point, since it is a PIVOT.
What I was trying to achieve was to guarantee (therefore the requirement to put the code in the trigger) that when new records are added to the tables that the PIVOT is based on then the SQL in the view would be updated to return the extra column. By executing an ALTER VIEW and setting the SQL for the view to "Select * From ...." it updates the view to include the new column returned by the SP. It works well, but not from a Trigger.
Tks
Doug
December 6, 2010 at 11:52 pm
Because of the choices MS made regarding pivot, this is a case of dynamic SQL being the best choice. I would pursue it that way in your stored procedure. You really don't want to be modifying schema on the fly, especially not from a proc that can be called by multiple connections simultaneously.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 6, 2010 at 11:53 pm
Yeah, figured as much and done so already, just thought I'd put it out there to see if there was a solution.
Tks.
December 7, 2010 at 11:32 am
I was trying to do the same thing and ran into the same problem. The cause was a GO statement that I included in my dynamic SQL. The GO ends the transaction, which is fine in a stored proc, but causes problems in a trigger.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2010 at 11:34 am
drew.allen (12/7/2010)
I was trying to do the same thing and ran into the same problem. The cause was a GO statement that I included in my dynamic SQL. The GO ends the transaction, which is fine in a stored proc, but causes problems in a trigger.Drew
To clarify this, a GO ends a batch, which will also end the definition of the proc. You cannot use a GO successfully outside of SSMS.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 1:20 pm
I've done something exactly like this...based on the rows in a table, a view with dynamic pivots gets altered.
in my case, i write to an events table from within the trigger; all it does is report which view needs to be recalculated, and two dates...created date and rebuild date(which is null).
then a schedule job which scans the events table every 10 minutes for all rows were the rebuildDT is null;
so that job will re-calculate the view definition and perform the DDL via dynamic SQL, and then update the events table to put the rebuildDT in the table to signify the work is complete.
Lowell
December 7, 2010 at 2:09 pm
Craig Farrell (12/7/2010)
drew.allen (12/7/2010)
I was trying to do the same thing and ran into the same problem. The cause was a GO statement that I included in my dynamic SQL. The GO ends the transaction, which is fine in a stored proc, but causes problems in a trigger.Drew
To clarify this, a GO ends a batch, which will also end the definition of the proc. You cannot use a GO successfully outside of SSMS.
How does the GO end the proc definition, when it's not executed until the the proc is executed?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2010 at 2:16 pm
drew.allen (12/7/2010)
Craig Farrell (12/7/2010)
drew.allen (12/7/2010)
I was trying to do the same thing and ran into the same problem. The cause was a GO statement that I included in my dynamic SQL. The GO ends the transaction, which is fine in a stored proc, but causes problems in a trigger.Drew
To clarify this, a GO ends a batch, which will also end the definition of the proc. You cannot use a GO successfully outside of SSMS.
How does the GO end the proc definition, when it's not executed until the the proc is executed?
Drew
Run this sample, it'll show you what I mean. GO cannot be part of the proc.
CREATE PROC proctest
AS
select 'begin proc'
SELECT 1
SELECT 2
GO
SELECT 3
GO
EXEC proctest
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 3:07 pm
There is no "GO" in my code, so must be something else. Thanks for the suggestion though.
Cheers
Doug
December 7, 2010 at 3:25 pm
doug.milostic (12/7/2010)
There is no "GO" in my code, so must be something else. Thanks for the suggestion though.Cheers
Doug
That was for Drew, Doug, since he specifically asked. No worries. Definately look into Lowell's suggestion above if you need to continue with the actual direct schema modification. Looks like the best way to avoid the concurrency issues.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2010 at 3:04 pm
Craig Farrell (12/7/2010)
Run this sample, it'll show you what I mean. GO cannot be part of the proc.
I know what YOU mean. What I mean is
CREATE PROC TEST_THIS
AS
DECLARE @sql nvarchar(200)
SET @sql = 'SELECT ''Begin Execution'' SELECT * FROM Information_Schema.Tables GO'
EXEC sp_executesql @sql
How does the 'GO' in the dynamic @sql stop the proc from compiling correctly? It doesn't, because it compiles and executes just fine on my system. The 'GO' does cause a problem when used in dynamic sql in a trigger, but it's not because it doesn't compile correctly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 8, 2010 at 3:33 pm
drew.allen (12/8/2010)
How does the 'GO' in the dynamic @sql stop the proc from compiling correctly? It doesn't, because it compiles and executes just fine on my system. The 'GO' does cause a problem when used in dynamic sql in a trigger, but it's not because it doesn't compile correctly.
Drew
OOOOOOOOOOoooooooooooooooohhhhhhhhhhh. Sorry, completely misunderstood what you meant.
I'm not entirely sure why the GO works in one and not the other, it's really not supposed to work in any of them at all. sp_executesql doesn't want to support multiple batches, so I had thought it was coded to exclude it at all times.
Which, apparently... It doesn't. Since this works too:
CREATE PROC TEST_THIS
AS
DECLARE @sql nvarchar(200)
SET @sql = 'SELECT ''Begin Execution'' SELECT * FROM Information_Schema.Tables GO SELECT * FROM Information_Schema.Tables'
EXEC sp_executesql @sql
GO
But this doesn't:
CREATE PROC TEST_THIS
AS
DECLARE @sql nvarchar(200)
SET @sql = 'SELECT ''Begin Execution'' SELECT * FROM Information_Schema.Tables GO 2 SELECT * FROM Information_Schema.Tables'
EXEC sp_executesql @sql
GO
Though, it will compile without error. So... I have no idea.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 8, 2010 at 5:04 pm
Hi Doug, if you still have trouble, could you try my test script pls?
I did not seem to have problems in updating views on the fly using trigger in my test. If I miss any point, please let me know.
My test sequence is very simple and descriptive.
1) Create a table.
2) Create an UPDATE trigger for the table.
3) Create the original View.
4) Insert test data.
5) Update the table - this will start the trigger.
6) See the changed view.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblTest]') AND type in (N'U'))
DROP TABLE [dbo].[tblTest]
CREATE TABLE [dbo].[tblTest](
[sInfo] [varchar](10) NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[trg_tblTest_Update] ON [dbo].[tblTest]
FOR UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(200)
,@Info Varchar(10)
IF Update([sInfo])
BEGIN
SELECT @info=[sInfo]
FROM DELETED
IF @info='ABC'
SET @sql = 'ALTER VIEW View1 AS SELECT ''New Col1'' [NewCol1]'
ELSE
IF @info='DEF'
SET @sql = 'ALTER VIEW View1 AS SELECT ''New Col1'' [NewCol1],''New Col2'' [NewCol2] '
ELSE
IF @info='GHI'
SET @sql = 'ALTER VIEW View1 AS SELECT ''New Col1'' [NewCol1],''New Col2'' [NewCol2],''New Col3'' [NewCol3] '
EXEC sp_executesql @sql
END
END
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW [dbo].[View1]
GO
CREATE VIEW View1 AS SELECT 'Old View' AS [Col1], 'Old View' AS [Col2]
GO
INSERT INTO [tblTest]
SELECT 'ABC'
UNION
SELECT 'DEF'
UNION
SELECT 'GHI'
SELECT * FROM [tblTest]
SELECT * FROM View1
UPDATE [tblTest]
SET [sInfo]='1ABC'
WHERE [sInfo]='ABC'
SELECT * FROM View1
UPDATE [tblTest]
SET [sInfo]='1DEF'
WHERE [sInfo]='DEF'
SELECT * FROM View1
UPDATE [tblTest]
SET [sInfo]='1GHI'
WHERE [sInfo]='GHI'
SELECT * FROM View1
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply