use AdventureWorks2012 ;

---------------------------------------------------------------------------------
-- Abschnitt 13.6
exec sp_configure 'show advanced options', 1 ;
exec sp_configure 'nested triggers', 0 ;
reconfigure		

exec sp_configure 'show advanced options', 1 ;
exec sp_configure 'server trigger recusion', 0 ;
reconfigure	
go

---------------------------------------------------------------------------------
-- Abschnitt 13.7
create table datTest (
    id int primary key identity (1,1) ,
    feld varchar(250) not null ,
    ins datetime null ,
    insBy sysname null ,
    upd datetime null ,
    updBy sysname null
) ;		
go

create trigger iTest on datTest for
    insert
as
    begin
    update datTest
        set ins = getdate(),
            insBy = current_user ,
            upd = null,
            updBy = null
        where id in ( select id from inserted ) ;
    end
go

insert into datTest ( feld ) 
	values ( '1.zeile' ), ('2.zeile' ), ('3.zeile') ;

select * from datTest ;

create trigger uTest on datTest for
    update
as
    begin
    update datTest
        set upd = getdate(),
            updBy = current_user 
        where id in ( select id from inserted ) ;
    end
go

update datTest
    set feld = 'Geänderte Zeile'
        insBy = 'me'
    where id = 2;

select * from datTest ;
go

alter trigger uTest on datTest for
    update
as
    begin
    -- Feststellen, ob an ins oder insBy geändert wurde
    if ( update(ins) or update(insBy) )
        begin
        raiserror ( 'Änderungen an Systemfeldern nicht erlaubt.',
					15, 1 ) ;
        rollback ;
        return ;
        end

    -- Ausgabe der Datensatzählung durch den Trigger unterdrücken
    set nocount on ;

    update datTest
        set upd = getdate(),
            updBy = current_user 
        where id in ( select id from inserted ) ;
    end
go

update datTest
    set feld = 'Geänderte Zeile',
    where id = 2;

select * from datTest ;

---------------------------------------------------------------------------------
-- Abschnitt 13.7.2
create view Sales.CustomerOrders as
	select c.CustomerID,  count(*) as orders
		from Sales.Customer as c inner join
			Person.Person as p on
				c.CustomerID = p.BusinessEntityID inner join
			Sales.SalesOrderHeader as o on
				c.CustomerID = o.CustomerID
		group by c.CustomerID ;
select * from Sales.CustomerOrders ;
select CustomerID, Orders
	into Sales.aggCustomerOrders
	from Sales.CustomerOrders ;
go

/*
 * Fügt neue Rechnungen in die selbsterstellte Aggregattabelle
 * aggCutomerOrders ein.
 * 20141220 mw
 */
create trigger i_SalesOrderHeader 
	on Sales.SalesOrderHeader
	for insert
as
	begin
	
	update Sales.aggCustomerOrders
		set Orders += ( 
			select count(*) 
				from inserted as i 
				where i.CustomerID=aggCustomerOrders.CustomerID)
		where CustomerID in (
			select distinct CustomerID from inserted ) ;

	end
go

---------------------------------------------------------------------------------
-- Abschnitt 13.7.3
/*
 * Stellt fest, ob alle eingefügten Datensätze zu ein und
 * demselben Auftrag gehören
 * 20141220 mw
 */
create trigger iu_SalesOrderDetail 
	on Sales.SalesOrderDetail
	for insert, update
as
	begin
	
	if ( select count ( distinct SalesOrderID )
			from inserted ) > 1
		begin
		raiserror ( 'Details for one order only.', 15, 1 ) ;
		rollback ;
		end
	end
go

/*
 * Update bestimmter Spalten verhindern
 * 20141220 mw
 */
create trigger u_SalesOrderDetail 
	on Sales.SalesOrderDetail
	for  update
as
	begin

	-- Nur 1 Auftrag?: wird in iu_SalesOrderDetail gecheckt.

	-- SalesOrderDetailID unverändert?
	if ( select count(*) 
		from deleted as d inner join
			inserted as i on
				d.SalesOrderDetailID = i.SalesOrderDetailID ) !=
	   ( select count(*) from deleted )
		begin
		raiserror ( 'Cannot update SalesOrderDetailID.', 15, 1 ) ;
		rollback ;
		end

	-- SalesOrderID unverändert?
	if exists ( select * 
			from deleted as d inner join
				inserted as i on
					d.SalesOrderDetailID = i.SalesOrderDetailID
			where i.SalesOrderID != d.SalesOrderID )
		begin
		raiserror ( 'Cannot update SalesOrderID.', 15, 2 ) ;
		rollback ;
		end
	end
go

---------------------------------------------------------------------------------
-- Abschnitt 13.7.4
create trigger dTest on datTest instead of
    delete
as
  set nocount on ;
go

delete datTest ;

select * from datTest ;

---------------------------------------------------------------------------------
-- Abschnitt 13.7.5
create table datTestDetail (
    id int primary key identity (1,1),
    detail varchar(250) ,
    testId int not null references datTest (id)
) ;

insert into datTestDetail ( detail, testId )
    values ('Ein Detail', 1),
        ('Noch ein Detail', 1),
        ('Erstes Detail', 2),
        ('Zweites Detail', 2),
        ('Erstes Detail', 3),
        ('Zweites Detail', 3),
        ('Drittes Detail', 3) ;

create view test
as
    select t.id as testId, feld, 
			d.id as detailId, detail
        from datTest as t inner join
            datTestDetail as d on        
                t.ID = d.testID ;
go

select * from test;

update test
    set feld += '!' ;

update test
    set detail += '!' ;

select * from test ;

update test
    set feld += '!',
        detail += '!' ;

create trigger uvTest on test instead of
update
as
    begin

    update datTest
        set feld = ( 
			select distinct feld 
				from inserted 
				where testId = datTest.id ) ;

    update datTestDetail
        set detail = ( 
			select detail 
				from inserted 
				where detailId = datTestDetail.id ) ;
    end
go
