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
>
