GRIDVIEW w/ a View Tbl & INSTEAD OF UPDATE Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

GRIDVIEW w/ a View Tbl & INSTEAD OF UPDATE Trigger

Author

Topic

LHendren
Starting Member

17 Posts

Posted - 2014-04-01 : 10:26:48

I have created a small project to learn how to UPDATE a database table which is part of a VIEW table that populates the GridView. I have been successful in updating a database table bound to the GridView. However, I am attempting the next level of displaying the VIEW and updating one of the TABLEs.

MyDatabase.Customer.ID and MyDatabase.CustomerRole.RoleID are the PK’s. I only want to update ONE database table; I am viewing two database tables with VIEW only to provide the user more detailed (background) information.

My two tables in the database are Customer and CustomerRole. My view in the database is a combination of the Customer and CustomerRole tables named ViewCustomer. My trigger is named ViewCustomerTrigger. Using the GridView displays the ViewCustomer view via ViewCustomerTableAdapter.Fill(MyDatabaseDataSet.ViewCustomer) , and I want to update the Customer table via CustomerTableAdapter.Update(MyDatabaseDataSet.Customer)

I cannot get the Customer table to Update when the form closes.

My TRIGGER is this:
USE MyDataBase
GO

CREATE TRIGGER ViewCustomerTrigger ON ViewCustomer

INSTEAD OF UPDATE
AS
IF IS_MEMBER ('db_owner') = 0
BEGIN
SET NOCOUNT ON

UPDATE dbo.Customer
SET Fname = I.Name,
SName = I.SName,
RoleID = I.RoleID
FROM INSERTED I JOIN Customer C ON I.ID = C.ID

PRINT 'You must ask your DBA to drop
or alter tables!'
ROLLBACK TRANSACTION

END
GO

My VB code is this:

Public Class Form1

Sub New()

InitializeComponent()

ViewCustomerTableAdapter.Fill(MyDatabaseDataSet.ViewCustomer) ' the
VIEW table

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

Close()

End Sub

Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As FormClosingEventArgs) Handles MyBase.FormClosing
Dim view As DevExpress.XtraGrid.Views.Base.BaseView = GridControl1.FocusedView
If Not (view.PostEditor() And view.UpdateCurrentRow()) Then
e.Cancel = True
Return
End If

CustomerTableAdapter.Update(MyDatabaseDataSet.Customer) ' the database table

End Sub

End Class

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-01 : 12:37:00

You're rolling back the UPDATE.

Also, you can't use a PRINT statement in a trigger.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources