Can't generate script for Triggers using DMO

For some reason, when I try to generate a script for Triggers off of a View
using DMO, I get back garbage like:
?VIEW1
That's the complete text coming back, nothing more. I can see it clearly
using MSEM just fine. Any thoughts?
Lee
Can you post your code, so that we can look into it?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Lee Grissom" <lee dot grissom at quest dot com> wrote in message
news:uyVQJqqdFHA.3932@.TK2MSFTNGP12.phx.gbl...
For some reason, when I try to generate a script for Triggers off of a View
using DMO, I get back garbage like:
?VIEW1
That's the complete text coming back, nothing more. I can see it clearly
using MSEM just fine. Any thoughts?
Lee
|||You mean scripting an INSTEAD OF TRIGGER on a VIEW?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Lee Grissom" <lee dot grissom at quest dot com> wrote in message
news:uyVQJqqdFHA.3932@.TK2MSFTNGP12.phx.gbl...
> For some reason, when I try to generate a script for Triggers off of a
> View using DMO, I get back garbage like:
> ?VIEW1
> That's the complete text coming back, nothing more. I can see it clearly
> using MSEM just fine. Any thoughts?
> --
> Lee
>
|||This works for me:
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
go
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
go
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
go
CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)
go
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
go
using System;
using System.Runtime.InteropServices;
using SQLDMO;
namespace SDN
{
class ScriptInsteadOfTrigger
{
[MTAThread]
static void Main(string[] args)
{
try
{
SQLServer2Class server = new SQLServer2Class();
server.LoginSecure = true;
server.Connect("(local)\\sql80", null, null);
Database2 database = (Database2) server.Databases.Item("testdb", "dbo");
View2 view = (View2) database.Views.Item("Employee", "dbo");
Trigger2 trigger = (Trigger2) view.Triggers.Item("IO_Trig_INS_Employee",
"dbo");
SQLDMO_SCRIPT_TYPE scriptType = SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default;
SQLDMO_SCRIPT2_TYPE script2Type =
SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default;
string sql = trigger.Script(scriptType, null, script2Type);
server.DisConnect();
}
catch(System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex);
}
catch(System.Exception ex)
{
Console.WriteLine(ex);
}
}
}
}
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Lee Grissom" <lee dot grissom at quest dot com> wrote in message
news:uyVQJqqdFHA.3932@.TK2MSFTNGP12.phx.gbl...
> For some reason, when I try to generate a script for Triggers off of a
> View using DMO, I get back garbage like:
> ?VIEW1
> That's the complete text coming back, nothing more. I can see it clearly
> using MSEM just fine. Any thoughts?
> --
> Lee
>

can't delete a row from primary table - is there an SP for thi

Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
updates and there was a trigger to rollback deletes. But I commented out al
l
of the triggers - all of them. So there are basically no triggers except fo
r
t_sometinging on PrimaryTbl
For Insert...
As
/* */
Return
I did that to all of the triggers so that nothing would fire. Right now I
am recreating the table with all the triggers, indexes, relationships,
constraints and so on and see if that table works (minus of course, the For
Delte As Rollback...)
"Tibor Karaszi" wrote:

> Jeff,
> As of 7.0 you can have several triggers of the same type on a table. And a
s of 2000, you can define
> which to fire first and which to fire last.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jeff Dillon" <jeffdillon@.hotmail.com> wrote in message
> news:OqFSPqreGHA.5040@.TK2MSFTNGP03.phx.gbl...
>
>A much easier way to disable triggers, that doesn't involve having to change
code, is to use ALTER TABLE ... DISABLE TRIGGER
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
> all
> of the triggers - all of them. So there are basically no triggers except
> for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the
> For
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||recreating the table seemed to do the trick. I was able to delete the row
from the re-created table.
Recreating the live table will be a bigger hassel because I will have to
stop replication first.
"Rich" wrote:
> Yes. The table in question had like 31 triggers - 11 or 12 inserts, some
> updates and there was a trigger to rollback deletes. But I commented out
all
> of the triggers - all of them. So there are basically no triggers except
for
> t_sometinging on PrimaryTbl
> For Insert...
> As
> /* */
> Return
> I did that to all of the triggers so that nothing would fire. Right now I
> am recreating the table with all the triggers, indexes, relationships,
> constraints and so on and see if that table works (minus of course, the Fo
r
> Delte As Rollback...)
>
> "Tibor Karaszi" wrote:
>|||yes, I have used that before, but I just needed to make sure there was
nothing in the triggers that could possibly run.
Well, as fate would have it, after I recreated my table and was able to
delete the desired row, I then readded the row and also re-added all the
triggers, relationships, etc. Now I can't delete the row again in the new
table. So I guess maybe there was nothing wrong with the original table.
So tommorrow I have to play the boring game of recreating the table, add the
row, delete the row, and keep adding triggers one at a time and relationship
s
until I can't delete the row and thus isolate the offending procedure,
relationship. I am already having indigestion thinking about it.
"Kalen Delaney" wrote:

> A much easier way to disable triggers, that doesn't involve having to chan
ge
> code, is to use ALTER TABLE ... DISABLE TRIGGER
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E95574E9-7F0F-474A-B870-7EDECDAC423C@.microsoft.com...
>
>|||Well, I figured out what the problem was in deleteing rows on my table. It
was the Instead Of Delete trigger. Even though I had commented out the body
and only had
Create Trigger...
Instead Of Delete
As
--
--
Return
This would not allow deletes on the table. When I removed the trigger
entirely, I was able to delete rows.
"Rich" wrote:
> yes, I have used that before, but I just needed to make sure there was
> nothing in the triggers that could possibly run.
> Well, as fate would have it, after I recreated my table and was able to
> delete the desired row, I then readded the row and also re-added all the
> triggers, relationships, etc. Now I can't delete the row again in the new
> table. So I guess maybe there was nothing wrong with the original table.
> So tommorrow I have to play the boring game of recreating the table, add t
he
> row, delete the row, and keep adding triggers one at a time and relationsh
ips
> until I can't delete the row and thus isolate the offending procedure,
> relationship. I am already having indigestion thinking about it.
> "Kalen Delaney" wrote:
>|||I think that is because the code in the trigger runs in place of your delete
statement (on the rows affected by it). Since there was no code, and no
action to perform, nothing was done with these rows.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:92D437D9-CFDB-45F4-8991-90C49BBFD1E7@.microsoft.com...
> Well, I figured out what the problem was in deleteing rows on my table.
It
> was the Instead Of Delete trigger. Even though I had commented out the
body
> and only had
> Create Trigger...
> Instead Of Delete
> As
> --
> --
> Return
> This would not allow deletes on the table. When I removed the trigger
> entirely, I was able to delete rows.
> "Rich" wrote:
>
new
table.
the
relationships
change
some
commented out
except
now I
relationships,
the
table. And
UPDATE,
I
delete/remove a
of
the
test
was
need to
from
is